• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to reverse a string in Excel

Jagdev Singh

Active Member
I am ever of VBA code

Function REVERSE(Str As String) As String

REVERSE = StrReverse(Trim(Str))

End Function

Say for Jaggi –

I have an excel formula – Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5, but I have to keep on changing it depend upon the length of the string. Is there any other easy formula in excel for this.

Regards,

JD
 

Luke M

Excel Ninja
Hi JD,

No, there is no native function in XL that would reverse. My recommendation would be to go with the UDF you have already.
 

Jagdev Singh

Active Member
Hi Luke and Hui

This Question just piched by one of my colleague and I was trying to find a way around in Excel. Thanks for sharing your valuable input on it. Even I tried many permution and combination, I would like to rest this query in piece now.:):)

Regards,
JD
 

jayalaxmi

Active Member
I am ever of VBA code

Function REVERSE(Str As String) As String

REVERSE = StrReverse(Trim(Str))

End Function

Say for Jaggi –

I have an excel formula – Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5, but I have to keep on changing it depend upon the length of the string. Is there any other easy formula in excel for this.

Regards,

JD
try this jaggu
IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))
 

Deepak

Excel Ninja
try this jaggu
IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))
Hi Jaya,

It's nice that u have taken penalty in this but what u have posted that is limited only to 5 chars & it can't be dynamic in any regards.

One have to change the same upon increasing the chars.
 

Jagdev Singh

Active Member
Hi Jaya

Even in my orginal post I have a excel formula - Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5


@Deepak mentioned it should be dynamic. That is the issue.

Regards,
JD
 

Hui

Excel Ninja
Staff member
JD

Yes we can hard code a reverse string formula for a known string length

But as yet there is no method of reversing an unknown length string without VBA
 

Deepak

Excel Ninja
@ All

The main reason why we don't have a subjected solution is that excel doesn't have a native CONCATENATE which is helpful in array functions.
 

David Evans

Active Member
Hi Luke and Hui

This Question just piched by one of my colleague and I was trying to find a way around in Excel. Thanks for sharing your valuable input on it. Even I tried many permution and combination, I would like to rest this query in piece now.:):)

Regards,
JD
Please continue the Pursuit of Hui's Holy Grail - it could be a movie some day. We can cast Hui as the Oracle and Chandoo as the young upstart ... :cool:
 

John()

Member
Nice I like this thread so thought id see how i would do it ... if i had to do it and hadnt access to VBA code i could depending on how many characters you can use in a formula make it dynamic up to string length of 255 but not going to try as it would be a massive formula and not sure that size of the formula would be allowed by excel.
So how would i make it dynamic ..... would use the choose function as this can ues 255 possibilities i believe
so would use = choose(len(A1),and formula to change here, here, here, ... so in theory could have it reverse a string up to 255 characters in length
as len(A1) would give length of string and resulting number would be used in the choose formula to pick the fromula at that location in the choose function ...
have supplied a spread sheet which uses it to reverse a cell contents up to 7 characthers dynamically at moment ... so whether you type in 3 or 7 characthers it will reverse it .... might if i have the patience keep adding to it and see how many characthers i can get it up to.

LOL in theory you could if excel accepted it, get unlimited size string .... eg if(len(A1)<=255,Choose(formula with 255 choices), then add another if with choose and so on ...... tried it by splitting my formula into two sections with a if less or =3 for one section and in false put other section.... not of sheet i uploaded though as i tried it after i posted ...... but somehow i dont think excell would allow a formula the size needed
 

Attachments

Last edited:

manish_1112

New Member
I am ever of VBA code

Function REVERSE(Str As String) As String

REVERSE = StrReverse(Trim(Str))

End Function

Say for Jaggi –

I have an excel formula – Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5, but I have to keep on changing it depend upon the length of the string. Is there any other easy formula in excel for this.

Regards,

JD
Why not try kutools. It has simple "reverse text" addon
 

Hui

Excel Ninja
Staff member
Manish
It probably does just what the REVERSE(Str As String) function does but won't be transportable to others without the addin
 

John()

Member
Ok .... had to see max char count i could get to using the choose function .... so got it dynamic up to 35 characters ..... which needed a formula of length 7,867 ... max allowed by excel is 8,192 ....
 

Attachments

gjw

New Member
assuming your string is in A1, the following array function does the trick:
{=TEXTJOIN("",TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1)))*-1+LEN(A1)+1,1))}
(don't enter the curly brackets, use control-shift-enter instead)

to break it down, assuming "abc" in A1:
INDIRECT("1:"&LEN(A1)) returns 1:3
ROW(1:3) returns array {1,2,3}
{1,2,3}*-1+LEN(A1)+1 subtracts each element from 4 to reverse the array: {3,2,1}
MID(A1,{3,2,1},1) creates array {"c";"b";"a"} by starting at location 3 and returning 1 character, then location 2 and returning 1 character,...
TEXTJOIN("",TRUE,{"c";"b";"a"}) concatenates the array back to a string with a null delimiter
 
Top