MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Separating one number to two.


Posted by Marianne on January 12, 2001 6:25 PM

Is there a way to take one 10-digit number and make it an 8-digit number in one cell (the first 8 digits) and a 2-digit number in a second cell (the last 2 digits). For instance:

Cell A1 = 1234567890
Cell B1 = 12345678
Cell C1 = 90

Thank you,

Marianne


Posted by Aladin Akyurek on January 12, 2001 7:10 PM

Enter

B1 =MID(A1,1,8)
C1 =MID(A1,9,2)

Aladin

Posted by Dave Hawley on January 13, 2001 1:09 AM

....Or you could use:

=Left(A1,8)
=Right(A1,2)

Dave

  • OzGrid Business Applications

Posted by Marianne on January 13, 2001 3:38 PM

Both of these Ideas work great, but...

The problem I am having is that some of the numbers I enter begin with zero. Such as:

0001234567

These formulas don't except the zeros begin with the number that is not a zero. Is there a way for me to fix this?

Marianne

Posted by Aladin Akyurek on January 13, 2001 11:59 PM

Re: Both of these Ideas work great, but...

: Is there a way to take one 10-digit number and make it an 8-digit number in one cell (the first 8 digits) and a 2-digit number in a second cell (the last 2 digits). For instance: : Cell B1 = 12345678 : Cell C1 = 90 C1 =MID(A1,9,2)


Both sets of formulas expect 10-digit entries in column A.
If you enter 0001234567, say in A1, and this entry is displayed as 1234567, then the formulas will not work. If this is the trouble, then text-format column A first (via Format, Cells, choose Text).

Aladin