# 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.

#### Hui

##### Excel Ninja
Staff member
JD

This is the holy grail of Excel Functions

If you can crack it without using VBA or helper cells you may well go down in history

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

• David Evans

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

• Khalid NGO

#### 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.

Hi All

Regards,
JD

#### 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 ... • jayalaxmi and Khalid NGO

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

• 10.6 KB Views: 8
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

• manish_1112

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

• 10.7 KB Views: 11

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

#### deciog

##### Active Member
If I understand you can use it this way, check it out.

Decio

#### Attachments

• 10.9 KB Views: 9