Separating digits

**wesgerl** · 03-30-2005, 05:14 PM

Is there a way in Excel to separate out part of a number? For example, if I have the number 154000. I want to be able to write a formula that extracts the 50000, but not get any of the other numbers. I want to be able to do this for each place in the number. If anyone knows of a way to do this, I would be grateful. Thanks!

**swatsp0p** · 03-30-2005, 05:41 PM

Assuming your number (154000) is in cell A10, the following formula will return 100000:

=(--MID(A10,FIND(RIGHT(A10,6),A10),1)*100000)

=(--MID(A10,FIND(RIGHT(A10,5),A10),1)*10000) will return 50000

=(--MID(A10,FIND(RIGHT(A10,4),A10),1)*1000) will return 4000

and so on....

note the use of the double negative that forces Excel to take the Text value returned by the MID and RIGHT functions and treat them as numeric values.

Good Luck

Bruce

**Ron Coderre** · 03-30-2005, 05:50 PM

Here's what I quickly came up with:

Put 1234567 in Cell A1.

Then in Cells B1 through B7 put:
7
6
5
4
3
2
1

Then put this formula in Cells C1 and copy it through C7:
=FLOOR(MOD($A$1,10^B1),10^(B1-1))

It may not be exactly what you're looking for but maybe it will put you on the right track.

Regards,
Ron