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:
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,268
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
46,268
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top