Join three columns containing phone number and format as (000) 000

[Guest]

I have a large spreadsheet where the phone number is split into three
columns. I want to combine these three columns into one and format as a phone
number. Also, the last column is dropping leading zero. I need it to keep the
leading zero.

 

[Max]

Assuming the 3 cols are cols A to C, data in row1 down, eg:

123 456 789
etc

where col C is supposed to be a 4 digit #
with leading zero where applicable: 0789
(leading zero was dropped in the data)

Put in D1:
="("&TEXT(A1,"000")&") "&TEXT(B1,"000")&TEXT(C1,"0000")
Copy D1 down

For the sample data, D1 will return: (123) 4560789

Adapt to suit ..

 

[Guest]

Assume cell A1 = "123", B1= "345", and C1="0145"
This expression =CONCATENATE(A1&" "&B1&" "&C1) in another cell will render
"123 345 0145"

 

[Guest]

If you want the parentheses, then: =CONCATENATE("("&A1&") "&B1&" "&C1)

 

[Guest]

This is great I've been trying to figure it out for a while BUT...now I'm
trying to use the resulting number (the phone #) to do a VLOOKUP and it won't
work because it is text...is there an easy way to make this compatible for a
VLOOKUP (without manually changing each one)
cheers,

 

[Max]

Can you post some sample data of the lookup values, and the lookup column in
the table array (1st col) ?