excell number formatting

F

FRISBIE RGW

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

BoniM

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

Duke Carey

=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,"-",""))
 
R

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.
Click to expand...

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
 
Top