Welcome Guest
You last visited January 17, 2017, 1:54 am
All times shown are
Eastern Time (GMT-5:00)

Excel Question for you Brainiacs!

Topic closed. 9 replies. Last post 8 years ago by KnuckleHead.

 Page 1 of 1
Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: May 3, 2009, 8:54 pm - IP Logged

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

We are all Lucky... just some of us don't realize it!

New Jersey
United States
Member #17843
June 28, 2005
50995 Posts
Offline
 Posted: May 3, 2009, 9:10 pm - IP Logged

Lottery Post Search is YOUR Friend:

It pays to review Previous Threads!

A mind once stretched by a new idea never returns to its original dimensions!

Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: May 3, 2009, 9:36 pm - IP Logged

Lottery Post Search is YOUR Friend:

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.

We are all Lucky... just some of us don't realize it!

Gent
Belgium
Member #73987
April 30, 2009
33 Posts
Offline
 Posted: May 4, 2009, 4:11 am - IP Logged

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

Pennsylvania
United States
Member #2218
September 1, 2003
5396 Posts
Offline
 Posted: May 5, 2009, 12:07 am - IP Logged

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))))

CA
United States
Member #2987
December 10, 2003
832 Posts
Offline
 Posted: May 9, 2009, 9:50 am - IP Logged

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.

Blessed Saint Leibowitz, keep 'em dreamin' down there.....

Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

=^.^=

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 11, 2009, 10:05 am - IP Logged

Good morning all,

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...

The only DUMB question is the one question you DID NOT ask...

New Mexico
United States
Member #58526
February 18, 2008
683 Posts
Offline
 Posted: May 11, 2009, 10:33 am - IP Logged

Good morning all,

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

New Jersey
United States
Member #17843
June 28, 2005
50995 Posts
Offline
 Posted: May 11, 2009, 10:48 am - IP Logged

Good morning all,

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...

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

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 11, 2009, 12:59 pm - IP Logged

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.

The only DUMB question is the one question you DID NOT ask...

 Page 1 of 1