How to Reverse Text in Excel without VBA

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

 

[Sean Timmons]

Reverse as in take each character and put in opposite order So abc would be
cba?

 

[Sheikh Saadi]

that's corrat... excatly like that...

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

is there any function or combination of funcitons?

 

[Pete_UK]

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

 

[Harlan Grove]

that's corrat... excatly like that...

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

is there any function or combination of funcitons?

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.

 

[T. Valko]

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.

 

[Sheikh Saadi]

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.

 

[Pete_UK]

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

 

[Ashish Mathur]

Hi,

You may download and install the addin from
http://xcell05.free.fr/morefunc/english/index.htm and then use the
TEXTREVERSE(cell_ref) function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[p45cal]

The likes of
MID($I$22,{4,3,2,1},1)
produces an array of characters being the reverse of the 4 characters
in I22.
If only I could concatenate them (*concatenate* is not an array-aware
function), I'd then look at getting the *{1,2,3,4}* from the likes of
C*olumn(A)* or *Row(1:4)* within the formula.

BTW:
Very simple UDF isFunction ReverseText(myText)
ReverseText = StrReverse(myText)
End Functionused
=REVERSETEXT(I22)
in a worksheet.