Move 1st X Digits Of Number To End

cnsxad · Feb 20th 2008

I need a formula to extract the first two numbers and move them to the back of the number remaining. For example, the original number is 235871, the result would be 587123. My numbers always have six digits but may at times have seven. Can anyone help?

Thanks!
cnsxad

Brian Walters · Feb 20th 2008

Re: Extract Numbers And Move To End Of Number

Try this =RIGHT(A1,LEN(A1)-2)&LEFT(A1,2)

cnsxad · Feb 21st 2008

Re: Move 1st X Digits Of Number To End

It worked!! I am not savy with the RIGHT, LEFT, LEN, MID command and needed this formula fast!! Thank you so much!
:tumble:

cnsxad

**Krishnakumar** · Feb 22nd 2008

Re: Move 1st X Digits Of Number To End

Hi,

Or..

=REPLACE(A1,1,2,"")&LEFT(A1,2)

=MID(A1,3,7)&LEFT(A1,2)

HTH

cnsxad · Feb 29th 2008

Re: Move 1st X Digits Of Number To End

All of these formulas work unless the number begins with a zero and ends with a zero. I have made a custom format to try to fix this by having my A1 cell formatted as 0####0. But this still does not work. I need all of the zeros to be in my final number.

Example:
Original # Final # needed
A1 B1
029430 943002

Any help?

Thanks!
cnsxad

daddylonglegs · Mar 1st 2008

Re: Move 1st X Digits Of Number To End

Try

=MID(REPT(TEXT(A1,"000000"),2),3,6)

cnsxad · Mar 3rd 2008

Re: Move 1st X Digits Of Number To End

This formula works great . . . but I have one more monkey wrench. Some of my numbers have 7 digits. Is there a formula that I can use so that I don't have to manually go through and change the 6 to a 7 in the formula. I'm sorry that I forgot to include this in my eariler posting.

Example:
514207 would be 420751
4512202 would be 1220245

Thank you for all of your help!
cnsxad

daddylonglegs · Mar 3rd 2008

Re: Move 1st X Digits Of Number To End

Assuming all your 7 digit numbers actually have 7 digits (i.e. they're not 6 digit numbers formatted to display with a leading zero) try

=IF(LEN(A1)=7,MID(A1&A1,3,7),MID(REPT(TEXT(A1,"000000"),2),3,6))

cnsxad · Mar 5th 2008

Re: Move 1st X Digits Of Number To End

Thank you! This formula works great! Except for numbers that do have 7 digits and a leading zero because when changed with the formula the zero is actually the sixth digit.

Example:
0812422 changed to 1242208

Can I format my original column a certain way to display the leading zeros or something?

Why does Excel hate leading zeros so much?!? Zeros are a fact of life and math!

Any further help would be greatly greatly appreciated!!! Thank you!

cnsxad

**PCI** · Mar 5th 2008

Re: Move 1st X Digits Of Number To End

Just change the format to a custom one and enter the number of 0 in type egal to the number of digits you want: type 0000000 will display 0001234 for 1234

daddylonglegs · Mar 5th 2008

Re: Move 1st X Digits Of Number To End

You probably need to format as text

A formula, in general, can't detect a number format of another cell so the problem would be that if you have this value in A1

123456

there is no way for a formula to detect that you have the cell formatted as 0000000 to display with a leading zero or not.

If you pre-format your column as text then you can input leading zeroes that will be recognised (or input with an apostrophe at the beginning, i.e. '0123456, the apostrophe indicates text and won't display)

then the zeroes exist as part of the value rather than the formatting so you can then just use this formula

=REPLACE(A1&LEFT(A1,2),1,2,"")

Move 1st X Digits Of Number To End

Participate now!

Share