Excel 2007 : How to append zeroes and extract middle digits

**whattodo** · 12-27-2010, 07:00 AM

I'm supposed to generate random numbers using the mid square method. Initially, I have a 4-digit number which I'm supposed to square. Then I have to extract the middle digits.I square this again and extract the middle digits. The problem is, I don't always get 8 digits when I square it. I'm supposed to append zeroes to the left of the string to make it exactly 8 digits, but I don't know how to do it.

example:

8065 65044225
0442 195364
5364 28772496

As you can see, I'm supposed to get 1953 instead of 5364. I really don't know what to do, please help. thanks!

**arthurbr** · 12-27-2010, 07:34 AM

Hi and welcome to the board supposing the first number squared is in B1, in A2 enter

to extract the numbers, use

=mid(text(b1,"00000000"),3,4)

that you can then square in b2, etc..

**whattodo** · 12-27-2010, 07:40 AM

It works perfectly! thank you so much!

**arthurbr** · 12-27-2010, 07:45 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Marcol** · 12-27-2010, 07:56 AM

With 8065 in A2

In B2

=A2^2

Drag/Fill Down

In A3

=MID(TEXT(B2,"00000000"),3,4)

Drag/Fill Down

You could format Column A > Custom "0000" and Column B >Custom "00000000", if you want

The formula in one step might be
In B2

=MID(TEXT(MID(TEXT(A2^2,"00000000"),3,4)^2,"00000000"),3,4)^2

Hope this helps

[EDIT]
Browser slow in refreshing yet again.

**squiggler47** · 12-27-2010, 08:17 AM

@marcol

=MID(RIGHT(rept(0,8)&(B1^2),8),3,4)

would be ok for doing it in 1 step!

The answer should be 4 digits rather than 8!
using the formula above, allows for different lengths :-

=MID(RIGHT(rept(0,a1*2)&(B1^2),a1*2),a1/2+1,a1)

Where A1 is the number of digits required in the random number! (note 2,4,6 are valid values)

**Marcol** · 12-27-2010, 08:43 AM

@ squiggler
Indeed it might, depends on your interpretation of "one step"

8065 returns 03814209 was my reading of the problem.

**squiggler47** · 12-27-2010, 08:54 AM

8065 returns 03814209 was my reading of the problem.

yes but the point was to return 8 digits, so that the middle 4 could be returned for the next line as its a repeated sequence!

So in this case 8065 would return 03814209 leading to 8142 to be the seed for the next line!

the fun starts after a few iterations it could hit a few of the values that make it loop indefinately between a few values!

Or hit a value that shrinks to zero!

**Marcol** · 12-27-2010, 09:59 AM

@ squiggler
At the risk of appearing dumb, where's the problem with this?
What can you see that that I'm missing?

The OP seems happy with Post#2

**squiggler47** · 12-27-2010, 10:58 AM

I assumed without checking that the 2 text statements where returning 8 digits, I couldn't see a need to have the 2 text statements otherwise, hence my reply!