software

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.

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:
=quot;(quot;amp;TEXT(A1,quot;000quot;)amp;quot;) quot;amp;TEXT(B1,quot;000quot;)amp;TEXT(C1,quot;0000quot;)
Copy D1 down

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

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Sharon Barrquot; lt;Sharon gt; wrote in message
...
gt; I have a large spreadsheet where the phone number is split into three
gt; columns. I want to combine these three columns into one and format as a
phone
gt; number. Also, the last column is dropping leading zero. I need it to keep
the
gt; leading zero.
Assume cell A1 = quot;123quot;, B1= quot;345quot;, and C1=quot;0145quot;
This expression =CONCATENATE(A1amp;quot; quot;amp;B1amp;quot; quot;amp;C1) in another cell will render
quot;123 345 0145quot;
--
C G Bartonquot;Sharon Barrquot; wrote:

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

If you want the parentheses, then: =CONCATENATE(quot;(quot;amp;A1amp;quot;) quot;amp;B1amp;quot; quot;amp;C1)
--
C G Bartonquot;Sharon Barrquot; wrote:

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

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,

quot;Maxquot; wrote:

gt; Assuming the 3 cols are cols A to C, data in row1 down, eg:
gt;
gt; 123 456 789
gt; etc
gt;
gt; where col C is supposed to be a 4 digit #
gt; with leading zero where applicable: 0789
gt; (leading zero was dropped in the data)
gt;
gt; Put in D1:
gt; =quot;(quot;amp;TEXT(A1,quot;000quot;)amp;quot;) quot;amp;TEXT(B1,quot;000quot;)amp;TEXT(C1,quot;0000quot;)
gt; Copy D1 down
gt;
gt; For the sample data, D1 will return: (123) 4560789
gt;
gt; Adapt to suit ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Sharon Barrquot; lt;Sharon gt; wrote in message
gt; ...
gt; gt; I have a large spreadsheet where the phone number is split into three
gt; gt; columns. I want to combine these three columns into one and format as a
gt; phone
gt; gt; number. Also, the last column is dropping leading zero. I need it to keep
gt; the
gt; gt; leading zero.
gt;
gt;
gt;

Can you post some sample data of the lookup values, and the lookup column in
the table array (1st col) ?
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;dvnmayaquot; gt; wrote in message
...
gt; This is great I've been trying to figure it out for a while BUT...now I'm
gt; trying to use the resulting number (the phone #) to do a VLOOKUP and it
won't
gt; work because it is text...is there an easy way to make this compatible for
a
gt; VLOOKUP (without manually changing each one)
gt; cheers,