What I am trying to do is to split any number into individual digits using a formula. Each individual digit of that number would occupy its own cell. Some numbers given to me are just straight numbers. Others have commas and/or decimal points. But, for all, I just want numbers in each cell. No commas, no spaces, dollar signs, decimal points…etc. So if I’m given…
26118
it will be separated into
2 6 1 1 8
for each cell
44,246
it will be separated into
4 4 2 4 6
for each cell
188,562.22
it will be separated into
1 8 8 5 6 2 2 2
for each cell
Being able to make Numbers into individual digits and placing them into individual cells with a formula would be great.
When doing a Numerology profile. It would make it so much easier to enter their numerical data. And have their numbers simply reduced to the root numbers. In the future, I hope to create a form that calculates things as they are filling it out. Saving time saves money.
—–
Xavier Flórez
To do this, I would split up the task into first removing the commas and periods, and then getting each character.
So if the value is in B2, then in C2 you can use SUBSTITUTE to get rid of the commas. Like this:
SUBSTITUTE(B2, ",", "")
So that just will replace each comma with nothing.
But you need to also get rid of the periods. So you'll need to nest that inside another SUBSTITUTE that does that:
SUBSTITUTE( SUBSTITUTE(B2, ",", ""), ".", "")
So the inner SUBSTITUTE takes 188,562.22 and returns the 188562.22, then the outer substitute takes that and returns 18856222.
Now in D2 you can easily get the first character using the MID function. So:
MID(C2,1,1)
This just gets the characters starting at character 1 and just returning 1 character. So 1.
Then in E2, you'd have the same thing, but the second character:
MID(C2,2,1)
Then you just continue from F2 and so on, always looking at C2, but increasing the second parameter by 1.
These formulas, SUBSTITUTE( SUBSTITUTE(B2, ",", ""), ".", "") and MID(C2,1,1), work great. But, how would I modify these formulas to show the number 0? So, for example...
$15.03 it will be separated into 1 5 0 3 for each cell
100,000.01 it will be separated into 1 0 0 0 0 0 0 1 for each cell
Xavier: Your example of 100,000.01 should work with those formulas. MID(C2,1,1) should give you 1. MID(C2,2,1) should give you 0.
As for $15.03 you are now adding another character into it. You either need to format the cells in B to not be currency so the $ characters are gone, erase them yourself, or add another nested SUBSTITUTE to get rid of the $ characters.
My mistake. I sent the wrong example. It's not the number 15.03 that has the problem. It's 15.00 . When a number ends in .00 . Numbers just sees the 15. When I remove the one cent from one hundred thousand. And it's just 100,000.00. The formula only sees the number 1 and the five zeros before the decimal point. So how would I get the formula to see, register, 00 after a decimal point? Can I even do that? Well...besides putting a digit after .00 like 1 to get .001 . Now the formulas see zeros.
Xavier: As long as the formatting of the cell. If the format shows the .00 then the MID function will see the two zeroes at the end. But if the cell isn't set for that format, then MID can't see them.
So I'm unclear on how to help you from here. I don't know why you have such a range of odd numbers, some with commas, some with decimals, some with currency symbols. It is hard to suggest something since I'm not seeing the whole picture here.
Good morning Gary. You have helped me a lot! Between our communications, and a three day weekend to work on this. This is the formula I pieced together from our exchanges...
=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14, "$", ""), ",", ""), ".", ""),1,1)
The range of numbers depends on what other people might enter into a cell. I am just trying to cover my bases. Again, I'm an amateur. Once I changed the format of the cell. It worked great. Thank you again Gary! Enjoy your holiday weekend!