Filling in data from subsequent columns

[tleber]

Hey everybody,

I am trying to fill in phone numbers from columns that are on my data set.  Below is what I am starting with:

	7042523124	4772273141
6437392425	7978623061	2445665089
3039088035	9389039890
3504114930		9452965175
2214755616	4538228136	3943427809
5134120650	4636093693
9098533233		2358304451

I want this data to look like this:

7042523124	4772273141
6437392425	7978623061	2445665089
3039088035	9389039890
3504114930	9452965175
2214755616	4538228136	3943427809
5134120650	4636093693
9098533233	2358304451

What function / expression should I be using?

[Inactive User · Accepted solution]

Answer attached.

[tleber]

Thank you!

Are you aware of any tutorials that can help me understand this better?

Thanks again!

[MarqueeCrew]

@tleber,

I have a simplistic solution for you.  In this solution I make the assumption that there are a limited # of phones and that each phone is exactly 10 digits (this is your example data).  With 700000 input records, this function completes in 1.4 seconds vs 6.0 seconds in @Inactive User's dynamic solution.  

• Make sure all input is STRING
• Trim the data (you had spaces in the field)
• Formula
  • Concatenate all of the fields into 1 (P1+P2+P3)
  • Parse the phones out of the P1P2P3

Since each phone in the example is 10 digits, we take the leftmost 10 as P1, the second 10 as P2 and the third 10 as P3.

If phone #'s don't comply with this standard then the solution won't work as written.  You would need to use the following formula to create the 10 digit phones in P1P2P3:

Padleft([Field1],10," ")+Padleft([Field2],10," ")+Padleft([Field3],10," ")

and then trim all of the output data as you parse it with a TRIM() function like this:

TRIM(Substring([P1P2P3],10,10)

Cheers,

Mark