Rearranging order of digits in a cell

[sk81222]

Hey everyone,

I have a long list of these. what I need to do is rearrange the order.

this is what i need each one to look like.

12M to be M12
18M to be M18
1M = M1
2Y = 2Y
3M = M3
3Y = 3Y
4Y = 4Y
6M = M6
9M = M9

this is the formula i tries to work out but to no avail.

=IF(RIGHT(2Y,1)=Y,CONCATENATE(RIGHT(2Y,1),LEFT(2Y,2)))

the problem is the the forumla doesnt even read the =Y and it doesn
account for the difference between 1M and 18M. but i think that can b
solved by =concatenate(left(2Y,1),0,right(2Y,1)) or something lik
that. im just having trouble incorporating it into the already plague
formula.

I would aprpeciate all the help I can get.

thank you

S

 

[Bearacade]

Try this..

=IF(OR(RIGHT(A1,1)="Y",RIGHT(A1,1)="M"),CONCATENATE(RIGHT(A1,1),LEFT(A1,LEN(A1)-1)))

HT

 

[bpeltzer]

From the sample you've given, it looks like you just need to take the final
character and shift it to the front. If so, then for the value in A1, the
formula would be
=right(a1,1)&left(a1,len(a1)-1)
If you enter that in B1, you'll have the result based on input from cell A1.
Then copy the formula down to subsequent rows to get the rest of the list
rearranged.

 

[CLR]

Another way...........

=(MID(A1,LEN(A1),1)&(LEFT(A1,LEN(A1)-1)))

Vaya con Dios,
Chuck, CABGx3

 

[sk81222]

Thanks everyone for the help.

what is the LEN function?

all the best,

S

 

[CLR]

You're welcome...........

This from HELP...
LEN
Returns the number of characters in a text string.
Syntax
LEN(text)
Text is the text whose length you want to find. Spaces count as characters.
Examples
LEN("Phoenix, AZ") equals 11
LEN("") equals 0


hth
Vaya con Dios,
Chuck, CABGx3

 

[jpreman]

I have tried all the solutions provided. However, I had to re-key in the data
for the formula to show the results. If not either it is show the identical
figure or returning FALSE as the results. Can anyone please let me know what
I need to do to get the results without re-keying in the data.

Thanks

 

[SteveG]

You may have some preceding or trailing spaces which would return some
unfavorable results since although you can not see them, the formula
will recognized them. Try,

=RIGHT(TRIM(A1),1)&LEFT(A1,LEN(TRIM(A1))-1)

HTH
Steve

 

[SteveG]

Slight change,

=RIGHT(TRIM(A1),1)&LEFT(TRIM(A1),LEN(TRIM(A1))-1)

HTH

Steve

 

[jpreman]

You are right Steve, indeed there were some spaces. It works fine now.

Thansk a lot.

 

[SteveG]

You're welcome. Thanks for the feedback.

Regards,

Steve