| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
| number format | Excel Discussion (Misc queries) | |||
| concatenating and formatting area code and phone number columns | Excel Worksheet Functions | |||
| Phone number format from 000.000.0000 to (000)000-0000 | Excel Discussion (Misc queries) | |||
| Telephone number format | Excel Discussion (Misc queries) | |||
Linear Mode
