Another quick spreadsheet to show off the capabilities of Excel.
Site visitor Jamie left the following comment on my recent post, “Random Number Generator for Excel“:
maria dont spose u know how to split a four digit number like 2365 into 4 different numbers 2 3 6 5 in all different cells in excel without having to do it manually just wandering please tell me if you know a way thanks
I had an idea of how to do this with the MID function, which is a text function. I just didn’t know if I had to convert the starting number to text before extracting the digits.
So I whipped up the attached worksheet and popped in the formulas. The answer: no, you don’t need to convert the number to text first.
The MID function has 3 arguments:
- text is the text you want to extract characters from. For this example, it’s the four-digit number.
- start_num is the number of the first character in the string you want to extract. For this example, it’s the digit number (1 though 4).
- num_chars is the number of characters you want to extract. For this example, it’s 1.
You can examine the formulas in the spreadsheet for yourself to learn more. Download it here.
Hope this helps, Jamie!
thanks soo much worked like a charm will reccomend you to everyone who needs help thanks again
jamie
Hi maria,
Thanks for the help. it worked out fine. I am facing an issue when trying to sum up all the split digits. its giving me a 0 (zero) in xl 2007. and its doesnt seem to be getting resolved. Hope its not one more of the xl bugs.
Ranjith
if i open the xl sheet in xl 2007 then sum(b2:e2) is giving me a value of zero where as sum(mid(a2,1,1), mid(a2,2,1), mid(a2,3,1), mid(a2,4,1)) seems to be working.
Ranjith, I’m pretty sure that the reason for this is that you’re using the SUM function on text, not numbers. MID is a text function, so its results will be text.
Use “Value” to convert Text to numbers and Sum will work.
you can try this:
in cell a1 type any word you like…
in cell b1 type this :
=MID($A1;COLUMN()-1;1)
then drag it to the right… it’ll do the job.
greetings from Greece!
thank you .
Ευχαριστώ ( in greek.)