Welcome Guest
You last visited December 4, 2016, 11:27 pm
All times shown are
Eastern Time (GMT-5:00)

# Question for excel experts

Topic closed. 6 replies. Last post 10 years ago by retxx.

 Page 1 of 1
Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
 Posted: February 6, 2007, 4:10 pm - IP Logged

Every one is doing, Square roots & Pi's.

My number is 6082 and I did a square root which equals 7798.7178433381
Now I want to make these new numbers:
7798 - 7987 - 9871 - 8717 - 7178 - 1784 - 7843 - 8433 - 4333 - 3381
What I did was to take the first 4# then the next 4#, etc
What formulas would I use to extract these.

Thank you
Mickie

Mickie

United States
Member #41846
June 23, 2006
459 Posts
Offline
 Posted: February 6, 2007, 5:55 pm - IP Logged

Mickie

let me assume your # is in a1   you want your result to be in c1  and you want the 4 # starting at position 5

in c1 type       =mid(a1,5,4)                 cell a1 start position 5  4 #

enjoy

p8

Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
 Posted: February 6, 2007, 7:30 pm - IP Logged

Thank you!

You got my mind a flowing. Here is what happened and what I ended up with. Hope others enjoy on their endeavors of the sqrt and PI they are working on.

 Original # 6082 What it gave me 77987 7798.7178433381 7798 798. 98.7 8.71 .717 7178 1784 7843 8433 4333 3338 3381 What I wanted 7798 7987 9871 8717 7178 1784 7843 8433 4333 3338 3381 77987178433381 7798 7987 9871 8717 7178 1784 7843 8433 4333 3338 3381 Left 7798 Right 7178433381 Concentrate 77987178433381 NEW =CONCATENATE(LEFT(B4,4),RIGHT(B4,10))

Here is you final formula: =CONCATENATE(LEFT(B4,4),RIGHT(B4,10))

Pk 4
Original at A3
=LEFT(B4,4)
=MID(\$B4,1,4)  --Repeat across for as many numbers you want--
=CONCATENATE(LEFT(B4,4),RIGHT(B4,10))

Pk3
Original at A3
=LEFT(B4,3)
=MID(\$B5,1,3)  --Repeat across for as many numbers you want--
=CONCATENATE(LEFT(B4,3),RIGHT(B4,11))

 Original # 682 261 261.151297144012 261 615 151 512 129 297 971 714 144 440 401 012 26151297144012 Left 261 Right 51297144012 Concentrate 26151297144012 NEW =CONCATENATE(LEFT(B4,3),RIGHT(B4,11))

Mickie

Mickie

BOSTON
United States
Member #48
September 9, 2001
3584 Posts
Offline
 Posted: February 7, 2007, 11:05 pm - IP Logged

hey mickie i am not very good at macros so could you or someone set up the macro in a spreadsheet so I can download it and see ifit works.Thanks

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: February 8, 2007, 8:51 am - IP Logged

Here is the Macro that will function IF your primary number is in Cell A1

=CONCATENATE(LEFT(TEXT((SQRT(A1)*1000000000000),"###############"),4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),2,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),3,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),4,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),5,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),6,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),7,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),8,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),9,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),10,4)," - ",MID(TEXT((SQRT(A1)*1000000000000),"###############"),11,4))

To make this work for a Pick 3, change all the 4's to a 3

Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
 Posted: February 8, 2007, 5:49 pm - IP Logged

SirMetro

Wow!! Thank you!
There is a small problem though, I did fix one part(I took and made them individual in cells)

see what the last number is? A 3 digit.

 0027

 5196 1961 9615 6152 1524 5242 2422 4227 2270 2707 707

Here is the original:
 0027 5196 - 1961 - 9615 - 6152 - 1524 - 5242 - 2422 - 4227 - 2270 - 2707 - 707The purple is the number I want to Sq root.Retxx as soon as I finish I will send you a copy. Mickie

Mickie

BOSTON
United States
Member #48
September 9, 2001
3584 Posts
Offline
 Posted: February 8, 2007, 10:45 pm - IP Logged

thanks Mickie,looking forward to good results I hope.

 Page 1 of 1