Excel Splitting Digits Example

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.

Split Digits worksheet

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!

Possibly Related Posts:

8 thoughts on “Excel Splitting Digits Example”

thanks soo much worked like a charm will reccomend you to everyone who needs help thanks again

jamie

Reply ↓

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

Reply ↓

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.

Reply ↓

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.

Reply ↓