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

# Another Excel Question...

Topic closed. 6 replies. Last post 6 years ago by winsumloosesum.

 Page 1 of 1
Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: July 27, 2010, 5:45 pm - IP Logged

If I have the number 7412 in cell "J3"

How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

Thanks

=IF(ISBLANK(J3),"",INT(J3/100))

=IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

=IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

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

Iron Station, NC
United States
Member #74828
May 21, 2009
69 Posts
Offline
 Posted: July 27, 2010, 7:25 pm - IP Logged

Hi-Lite the cell the numbers are in, click on Data, then Text to Columns, click the Fixed width , then next, click on to creat a breaking line click

at the desired position, (place a line between each didgit), then click finsh.

Hope this works for you

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: July 27, 2010, 9:37 pm - IP Logged

I assume you are looking for a formula

several ways to do this. my favorite is =mid(j3,1,1) for the first # then =mid(j3,2,1)  etc.

j3 is obviously cell reference. next digit defines the position you want to start, then 3rd # tells it how many #'s to select. by using 1 i get 1 number

New Jersey
United States
Member #17843
June 28, 2005
51064 Posts
Offline
 Posted: July 27, 2010, 11:58 pm - IP Logged

If I have the number 7412 in cell "J3"

How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

Thanks

=IF(ISBLANK(J3),"",INT(J3/100))

=IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

=IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

Lottery Post Forums Search is Your Friend:

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

Iron Station, NC
United States
Member #74828
May 21, 2009
69 Posts
Offline
 Posted: July 28, 2010, 8:28 am - IP Logged

You will not have to use a formula if you use the Data to Column, and you can do a whole column of numbers.  Work with it

and see how easy it is.

Member #19992
August 9, 2005
2867 Posts
Offline
 Posted: July 30, 2010, 1:21 am - IP Logged

You will not have to use a formula if you use the Data to Column, and you can do a whole column of numbers.  Work with it

and see how easy it is.

Thanks guys...

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

Pennsylvania
United States
Member #2218
September 1, 2003
5396 Posts
Offline
 Posted: July 30, 2010, 8:38 pm - IP Logged

If I have the number 7412 in cell "J3"

How do I formulate it so I can get the 7 in one cell, the 4 in another cell the 2 in another cell and the 1 in another cell?

These formulas get me the first 3 digits into seperate columns but how do I get the 4th digit...anyone know the formula for this one?

Thanks

=IF(ISBLANK(J3),"",INT(J3/100))

=IF(ISBLANK(J3),"",INT((J3-(K3*100))/10))

=IF(ISBLANK(J3),"",INT((J3-((K3*100)+L3*10))))

7412 (In Cell J3)
=INT(J3/1000)                                                ' Formula in cell K3
=INT((J3-(K3*1000))/100)                              ' Formula in cell L3
=INT((J3-((K3*1000)+(L3*100)))/10)            ' Formula in cell M3
=INT((J3-((K3*1000)+(L3*100)+(M3*10))))  ' Formula in cell N3

 Page 1 of 1