excell number formatting

[Guest]

I am trying to take 3 cells and merge the information into one cell while
changing the formatting.

enter 23, 45, 670 into 3 cells. Have a return of 023-045-670 in one cell.
I need to use this # as if it were 23045670 and be able to sort and add to
it. I know how to mege the cell it's the return of 023 from 23 that seems
most problematic.

 

[Guest]

If you want the number including dashes (with original numbers in A1,B1, and
C1):
=TEXT(A1,"000")&"-"&TEXT(B1,"000")&"-"&TEXT(C1,"000")
Without dashes:
=TEXT(A1,"000")&TEXT(B1,"000")&TEXT(C1,"000")

 

[Guest]

=text(cell1,"000")&"-"&text(cell2,"000")&"-"&text(cell3,"000")

This will be a text value. You can use it as a numeric value by

=VALUE(SUBSTITUTE(text value,"-",""))

 

[Ron Rosenfeld]

On Fri, 20 Apr 2007 11:12:01 -0700, FRISBIE RGW <FRISBIE

I am trying to take 3 cells and merge the information into one cell while
changing the formatting.

enter 23, 45, 670 into 3 cells. Have a return of 023-045-670 in one cell.
I need to use this # as if it were 23045670 and be able to sort and add to
it. I know how to mege the cell it's the return of 023 from 23 that seems
most problematic.

To combine the values, you can use this formula:

=SUMPRODUCT(A1:A3,{1000000;1000;1})

If the original values are in a ROW rather than a COLUMN, use comma separators
instead of semi-colons in the array constant.

Format the cell as:

Format/Cell/Number/Custom Type: 000-000-000


--ron