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

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.

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

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sharon Barr" <Sharon wrote in message
...
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.

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

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


"Sharon Barr" wrote:

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.

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

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


"Sharon Barr" wrote:

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.

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

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" wrote:

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sharon Barr" <Sharon wrote in message
...
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.

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

Can you post some sample data of the lookup values, and the lookup column in
the table array (1st col) ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dvnmaya" wrote in message
...
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,