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...

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")


--
Biff
Microsoft Excel MVP


"DavidJ726" wrote in message
...
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...

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 SpecialPhone number:

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

Result: (913) 449-0004



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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")


--
Biff
Microsoft Excel MVP


"DavidJ726" wrote in message
...
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...

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 :-)

"DavidJ726" wrote in message
...
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...

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DavidJ726" wrote in message
...
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 :-)

"DavidJ726" wrote in message
...
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...