Concatenate or not?

D

DavidJ726

I need to take the contents of three cells and combine them to form a
telephone #, but when a number in cell begins with 0's, the concatenation
function breaks, here's an example;

The cells (A5, B5 & C5) contain; 913 449 0004

The formula in D5 is; =CONCATENATE(A5,"- ",B5,"-",C5)

The result is 913-449-4 instead of the desired 913-449-0004

The only solution I found was to format the cells as text, but then the
worksheet is full of cells with the little triangles in the corner of each
cell. Currently the cells are set to custom formatting to display 4
numbers, but that doesn't help the result.

Any ideas?

Thanks,
David...
 
T

T. Valko

The cells (A5, B5 & C5) contain; 913 449 0004

Assuming C5 is custom number formatted as 0000.

=TEXT(A5,"000-")&TEXT(B5,"000-")&TEXT(C5,"0000")
 
T

T. Valko

P.S.

I'm not a "phone number expert". I don't know if the area code can start
with leading zeros or if the next 3 digits can have leading zeros. So, the
formula I suggested uses a TEXT function call for each set of numbers. If
the area code and the next set of digits can't have leading zeros then you
can reduce the formula to:

=A5&"-"&B5&"-"&TEXT(C5,"0000")

Result: 913-449-0004

Or, you could even use this formula and format as Special>Phone number:

=--(A5&B5&TEXT(C5,"0000"))

Result: (913) 449-0004
 
D

DavidJ726

Bill, Thanks for the quick response, as well as the solution(s). The 1st is
exactly what I needed, the 2nd answer was icing on the cake :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate end date and time 1
HELP please... easy function 3
Lookup Function help 1
Variable Sumproduct Range 6
Extracting data/using COUNTIFS 2
Opposite direction 9
CTRL SHIFT ENTER 2
concatenate 2

Top