Welcome Guest
You last visited December 7, 2016, 4:45 am
All times shown are
Eastern Time (GMT-5:00)

# need help with excel

Topic closed. 5 replies. Last post 8 years ago by Mickie.

 Page 1 of 1
Mount Morris
United States
Member #46750
September 27, 2006
139 Posts
Offline
 Posted: March 26, 2009, 7:57 pm - IP Logged

1: I need to see how to make formula for one cell  like   25= 7    63=9       64=0

2:  i want to break done a string of numbers  like 2698742563.2 but i only want some of them say 8 ,2,6

and the string is in one cell but need to put my pick in another cell for testing faster

Michigan
United States
Member #34209
March 1, 2006
265 Posts
Offline
 Posted: March 26, 2009, 10:14 pm - IP Logged

Try the following, breaking each number apart.

Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
 Posted: March 27, 2009, 9:57 am - IP Logged

Put it in A1, then in A2 put in this formula:

=MID(\$A\$1,1,1) and it will give you "2"

\$A\$1 is the cell your long number is
,1 is the position you want extracting (1st digit)
,1 is how many digits you want extracted.

For example:  =MID(\$A\$1,1,3) you would get "269"
=MID(\$A\$1,3,3) you would get "987"

Hope this helps
Mickie

Mickie

Mount Morris
United States
Member #46750
September 27, 2006
139 Posts
Offline
 Posted: March 27, 2009, 10:20 am - IP Logged

Mickie  that formula you posted works for me . Thanks

now see what curious2k3  posted it want work for me .

problem  ---  2479=22 sum == sum-4 iam trying to do all automatic with out typing in each digit to break it down using lottery math so when I get my xl done i can let people use it to see how it works in there state. lot of older people don't know where to put the numbers in

type in 4 digit  2479 = 4 may not be able to do it these way.???

New Member

Malaysia
Member #72778
March 27, 2009
9 Posts
Offline
 Posted: March 27, 2009, 12:30 pm - IP Logged

Mickie  that formula you posted works for me . Thanks

now see what curious2k3  posted it want work for me .

problem  ---  2479=22 sum == sum-4 iam trying to do all automatic with out typing in each digit to break it down using lottery math so when I get my xl done i can let people use it to see how it works in there state. lot of older people don't know where to put the numbers in

type in 4 digit  2479 = 4 may not be able to do it these way.???

hey hillbilly953,

1. If H2 Cell contains 2470
2. Format H2 as Text

3. Key in this formula in I2
=IF(LEN(H2=4);SUM(VALUE(MID(H2;1;1));VALUE(MID(H2;2;1));VALUE(MID(H2;3;1));VALUE(MID(H2;4;1)));SUM(VALUE(MID(H5;1;1));VALUE(MID(H5;2;1));VALUE(MID(H5;3;1))))

(I am using Open Office)

4. Result should get in I2 is 13
5. You could have another column doing 1+3=4 say as Cell J2
IF(LEN(i2=2);SUM(VALUE(MID(I2;1;1));VALUE(MID(i2;2;1)));SUM(VALUE(MID(i2;1;1));VALUE(MID(i2;2;1));VALUE(MID(i2;3;1)))

hope that helps.

dennon.

Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
 Posted: March 28, 2009, 8:32 am - IP Logged

Easy:

A1  2479

B1 =MID(\$A1,1,1)+MID(\$A1,2,1)+MID(\$A1,3,1)+MID(\$A1,4,1)

C1 =MOD(SUM(MID(\$B1,1,1)+MID(\$B1,2,1)),10)

A1             B1            C1

 2479 22 4

or send me your sheet, I will fix it the way you want. I have office 2003.

Have a good day
Mickie

Mickie

 Page 1 of 1