Rearrange data within a cell

jcurtoys

Board Regular
Joined
Oct 11, 2004
Messages
56
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!!!!! :pray:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,551
Office Version
  1. 365
Platform
  1. Windows
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,551
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 11, 2004
Messages
56
I haven't ran across that yet, but good point...I'll run a test and let you know
 

Forum statistics

Threads
1,141,143
Messages
5,704,537
Members
421,353
Latest member
jekoxien15

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top