Excel Question for you Brainiacs!Previous TopicNext Topic

What formula would I use to divide a 4 digit number (1234) into 4 different columns on a worksheet?

Your help is greatly appreciated!

Lottery Post Search is YOUR Friend:

http://www.lotterypost.com/thread/172280/1000196

It pays to review Previous Threads!

I tried using it...lol  I figured I had asked a similar question a while back but couldn't find it in the results.

I typed in excel help for formula's or something like that, but it never gave me anything useful.

Thanks for your help!

Hi Rakster, I'm not at all an excel expert, but hopefully I can help you with this:

In cell A1 is your four digit number: 1234

In cell B1, type the following formula: =INT(A1/1000)

In cell C1, type the following formula: =INT((A1-B1*1000)/100)

In cell D1, type the following formula: =INT((A1-B1*1000-C1*100)/10)

In cell E1, type the following formula: =A1-B1*1000-C1*100-D1*10

As a result, cells A1 to E1 should look like this:

1234        1          2          3          4

Assuming Cell C is where you enter the 4 digit combo...

in cell D ...  =INT(C2/1000)

in cell E ...  =INT((C2-(D2*1000))/100)

in cell F ...  =INT((C2-((D2*1000)+(E2*100)))/10)

in cell G ...  =INT((C2-((D2*1000)+(E2*100)+(F2*10))))

Alternately, if the 4-digit number is entered into Cell A1 -

In Cell A2: =LEFT(A1,1)*1
In Cell A3: =MID(A1,2,1)*1
In Cell A4: =MID(A1,3,1)*1
In Cell A5: =RIGHT(A1,1)*1

The "*1" part of the formula ensures the cell content will be numeric. If text is sufficient, then the "*1" may be omitted.

Good morning all,

I've been reading/watching this thread with interest.

Here's my question. Can a formula do the opposite? In other words, if you have 4 cells in a row and you want to tie those numbers into one cell without "summing" them, can that be done without formatting the cell to a "label"?

Curiously interested...

KnuckleHead, that can be done just by using "&".  Let's say your four numbers are in cells A1,B1,C1,D1........

the formula in E1       =A1&B1&C1&D1       would return all four numbers together as one

A2=3, B2=4, C2=5, D2=6 E2=+$A2*1000+$B2*100+$C2*10+$D2*1

It's not a formula I needed, I just wondered if it could be done. Now everyone who views this "original" post will be able to see how to formulate in both directions. The results for both formulas appear to be very simple.

Thank you for the quick response.