How to Reverse Text in Excel without VBA

  • Thread starter Thread starter Sheikh Saadi
  • Start date Start date
S

Sheikh Saadi

Hi,

i need to reverce text written in a cell without using VBA. I know how to
write a simple VBA function to do this job, but wanted to know if there is
any function available to do this, or even a combination of functions if
there are any.

A quick response would be much appriciated...

thanks...
 
Reverse as in take each character and put in opposite order So abc would be
cba?
 
that's corrat... excatly like that...

Example:
Hello Abc --->>> cbA olleH

is there any function or combination of funcitons?
 
Well, this is clumsy, but it will do it for up to 12 characters:

=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))&
IF(LEN(A1)<6,"",MID(A1,LEN(A1)-5,1))&
IF(LEN(A1)<7,"",MID(A1,LEN(A1)-6,1))&
IF(LEN(A1)<8,"",MID(A1,LEN(A1)-7,1))&
IF(LEN(A1)<9,"",MID(A1,LEN(A1)-8,1))&
IF(LEN(A1)<10,"",MID(A1,LEN(A1)-9,1))&
IF(LEN(A1)<11,"",MID(A1,LEN(A1)-10,1))&
IF(LEN(A1)<12,"",MID(A1,LEN(A1)-11,1))

I've manually split it so that you can see how to extend it for more
characters, but it is all one formula, assuming the text to reverse is
in A1.

Hope this helps.

Pete
 
Sheikh Saadi said:
that's corrat... excatly like that...

Example:
Hello Abc --->>>   cbA olleH

is there any function or combination of funcitons?
Click to expand...

Not generally, no. Brute force is the only thing that comes close.

With FOOBAR in cell X99, the formula

=LEFT(RIGHT(X99,1)&LEFT(RIGHT(X99,2))&LEFT(RIGHT(X99,3))
&LEFT(RIGHT(X99,4))&LEFT(RIGHT(X99,5))&LEFT(RIGHT(X99,6))
&LEFT(RIGHT(X99,7))&LEFT(RIGHT(X99,8))&LEFT(RIGHT(X99,9))
&LEFT(RIGHT(X99,10))&LEFT(RIGHT(X99,11))&LEFT(RIGHT(X99,12))
&LEFT(RIGHT(X99,13))&LEFT(RIGHT(X99,14))&LEFT(RIGHT(X99,15))
&LEFT(RIGHT(X99,16)),LEN(X99))

returns RABOOF. You could extend this further, but it'll run up
against the formula character limit, which means you can't reliably
reverse strings longer than 50 characters or so.
 
There is an add-in available that has functions that will do this.

However, if you go to the trouble of installing an add-in you may as well
use your own UDF.
 
Thanks Pete, but I know this workaounrd. also, this would not work as i dont
know if the characters are exactly 12 or less everytime.

As far as i found out, there is no build in function for this task.

Thanks for your response though.
 
I posted it so that you can easily see the structure of the formula,
to extend it if you need to. Just keep copying the IFs and change the
numbers in sequence. This will do the complete alphabet:

=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))&IF(LEN(A1)
<6,"",MID(A1,LEN(A1)-5,1))&IF(LEN(A1)<7,"",MID(A1,LEN(A1)-6,1))&IF(LEN
(A1)<8,"",MID(A1,LEN(A1)-7,1))&IF(LEN(A1)<9,"",MID(A1,LEN(A1)-8,1))&IF
(LEN(A1)<10,"",MID(A1,LEN(A1)-9,1))&IF(LEN(A1)<11,"",MID(A1,LEN
(A1)-10,1))&IF(LEN(A1)<12,"",MID(A1,LEN(A1)-11,1))&IF(LEN(A1)<13,"",MID
(A1,LEN(A1)-12,1))&IF(LEN(A1)<14,"",MID(A1,LEN(A1)-13,1))&IF(LEN(A1)
<15,"",MID(A1,LEN(A1)-14,1))&IF(LEN(A1)<16,"",MID(A1,LEN(A1)-15,1))&IF
(LEN(A1)<17,"",MID(A1,LEN(A1)-16,1))&IF(LEN(A1)<18,"",MID(A1,LEN
(A1)-17,1))&IF(LEN(A1)<19,"",MID(A1,LEN(A1)-18,1))&IF(LEN(A1)<20,"",MID
(A1,LEN(A1)-19,1))&IF(LEN(A1)<21,"",MID(A1,LEN(A1)-20,1))&IF(LEN(A1)
<22,"",MID(A1,LEN(A1)-21,1))&IF(LEN(A1)<23,"",MID(A1,LEN(A1)-22,1))&IF
(LEN(A1)<24,"",MID(A1,LEN(A1)-23,1))&IF(LEN(A1)<25,"",MID(A1,LEN
(A1)-24,1))&IF(LEN(A1)<26,"",MID(A1,LEN(A1)-25,1))

So that:

abcdefghijklmnopqrstuvwxyz

in A1 becomes:

zyxwvutsrqponmlkjihgfedcba

But it's not very nice !!

Pete
 
Here is a relatively simple formula to reverse a string up to 20 characters long in cell A1:

=MID(A1,20,1) & MID(A1,19,1) & MID(A1,18,1) & MID(A1,17,1) & MID(A1,16,1) & MID(A1,15,1) & MID(A1,14,1) & MID(A1,13,1) & MID(A1,12,1) & MID(A1,11,1) & MID(A1,10,1) & MID(A1,9,1) & MID(A1,8,1) & MID(A1,7,1) & MID(A1,6,1) & MID(A1,5,1) & MID(A1,4,1) & MID(A1,3,1) & MID(A1,2,1) & MID(A1,1,1)

You can extend this to allow for longer strings by copying the 19-10 range, pasting it at the start, and changing 19 to 29, 18 to 28, etc.

Note that this takes advantage of the fact that the MID() function in Excel (at least the 2011 version for Mac) will cheerfully return an empty string if you ask for a segment beyond the end of the string.
 
Last edited:
a way to avoid very long formulas and limitations:
B1: =MID($A$1;B2;1) B2: 1
C1: =MID($A$1;C2;1)&B1 C2:=B2+1
then drag as long as you want.

of course you may hide it on a new sheet.
The result is in the last column of line 1.
 
Try "=MID(A1,4-ROW(1:3),1)", substituting the length of the string + 1 for the number 4 and the length of the string for the number 3.
 
Hi,

A simple solution is to create a small recursive function like this:

Public Function RevText(s As String)
If Len(s) = 1 Then
RevText = s
Else
RevText = RevText(Mid(s, 2, 99)) & Left(s, 1)
End If
End Function

Have fun!
Jorge
 
Back
Top