Rearrange data within a cell

[jcurtoys]

I have a problem trying to change the way I receive some data. For example if the data in C2 = 392242 I need it to rearrange by dropping the first number then send the next three to the end. So I end up with 42922. Another wrench is that if the cell starts with a letter or has less then 6 places it needs to remain the same
HELP!!!!! ray:

 

[Peter_SSs]

Janna

If you are happy with the result of the rearrangement being text, then in D2 (copied down):
=IF(OR(ISTEXT(C2),LEN(C2)<>6),C2,RIGHT(C2,2)&MID(C2,2,3))

If you want the rearranged cells as numbers, then in E2 (copied down):
=IF(OR(ISTEXT(C2),LEN(C2)<>6),C2,(RIGHT(C2,2)&MID(C2,2,3))+0)

Mr Excel.xls
	B	C	D	E	F
1		Original	Text	Number
2		392242	42922	42922
3		H12345	H12345	H12345
4		56987	56987	56987
5		895623	23956	23956
6		100256	56002	56002
7
Rearrange

 

[NBVC]

Try:

=IF(ISNUMBER(LEFT(A1,1)+0),IF(LEN(A1)>=6,RIGHT(A1,2)&MID(A1,2,3),A1),A1)

copied down, where A1 houses 1st entry

 

[jcurtoys]

You guys are awesome NBVC idea worked great!!!!!!

 

[Peter_SSs]

Janna

Can the data ever be more than 6 characters? If so, what should happen with those?

If the data is always 6 characters or less, then I suggest this slightly more compact formula:
=IF(OR(ISTEXT(C2),LEN(C2)<6),C2,RIGHT(C2,2)&MID(C2,2,3))

 

[jcurtoys]

I haven't ran across that yet, but good point...I'll run a test and let you know