# Rearrange data within a cell

#### jcurtoys

##### Board Regular
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:

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
BCDEF
1OriginalTextNumber
23922424292242922
3H12345H12345H12345
4569875698756987
58956232395623956
61002565600256002
7
Rearrange

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

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

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))

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

Replies
4
Views
467
Replies
1
Views
302
Replies
4
Views
320
Replies
4
Views
327
Replies
1
Views
208

1,219,918
Messages
6,150,963
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back