PC Review


Reply
Thread Tools Rate Thread

How to Reverse Text in Excel without VBA

 
 
Sheikh Saadi
Guest
Posts: n/a
 
      2nd Sep 2009
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...
--
Sheikh Saadi
 
Reply With Quote
 
 
 
 
Sean Timmons
Guest
Posts: n/a
 
      2nd Sep 2009
Reverse as in take each character and put in opposite order So abc would be
cba?


"Sheikh Saadi" wrote:

> 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...
> --
> Sheikh Saadi

 
Reply With Quote
 
 
 
 
Sheikh Saadi
Guest
Posts: n/a
 
      2nd Sep 2009
that's corrat... excatly like that...

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

is there any function or combination of funcitons?

--
Sheikh Saadi


"Sean Timmons" wrote:

> Reverse as in take each character and put in opposite order So abc would be
> cba?
>
>
> "Sheikh Saadi" wrote:
>
> > 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...
> > --
> > Sheikh Saadi

 
Reply With Quote
 
Pete_UK
Guest
Posts: n/a
 
      2nd Sep 2009
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


On Sep 2, 10:13*pm, Sheikh Saadi <(E-Mail Removed)> wrote:
> that's corrat... excatly like that...
>
> Example:
> Hello Abc --->>> * cbA olleH
>
> is there any function or combination of funcitons?
>
> --
> Sheikh Saadi
>
>
>
> "Sean Timmons" wrote:
> > Reverse as in take each character and put in opposite order So abc would be
> > cba?

>
> > "Sheikh Saadi" wrote:

>
> > > 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 functionsif
> > > there are any.

>
> > > A quick response would be much appriciated...

>
> > > thanks...
> > > --
> > > Sheikh Saadi- Hide quoted text -

>
> - Show quoted text -


 
Reply With Quote
 
Harlan Grove
Guest
Posts: n/a
 
      2nd Sep 2009
Sheikh Saadi <(E-Mail Removed)> wrote...
>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.
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      2nd Sep 2009
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.

--
Biff
Microsoft Excel MVP


"Sheikh Saadi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...
> --
> Sheikh Saadi



 
Reply With Quote
 
Sheikh Saadi
Guest
Posts: n/a
 
      2nd Sep 2009
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.

--
Sheikh Saadi


"Sheikh Saadi" wrote:

> 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...
> --
> Sheikh Saadi

 
Reply With Quote
 
Pete_UK
Guest
Posts: n/a
 
      2nd Sep 2009
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



On Sep 2, 11:38*pm, Sheikh Saadi <(E-Mail Removed)> wrote:
> 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.
>
> --
> Sheikh Saadi
>
>
>
> "Sheikh Saadi" wrote:
> > 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 thereis
> > any function available to do this, or even a combination of functions if
> > there are any.

>
> > A quick response would be much appriciated...

>
> > thanks...
> > --
> > Sheikh Saadi- Hide quoted text -

>
> - Show quoted text -


 
Reply With Quote
 
Ashish Mathur
Guest
Posts: n/a
 
      3rd Sep 2009
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

"Sheikh Saadi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...
> --
> Sheikh Saadi


 
Reply With Quote
 
New Member
Join Date: Jan 2013
Posts: 1
 
      10th Jan 2013
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 by mfripp; 10th Jan 2013 at 10:08 PM..
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
reverse text without using word art Aussie_mum2 Microsoft Word Document Management 4 1st Nov 2008 03:29 PM
How to Reverse Contents in an Excel cell (Text or a Number) =?Utf-8?B?Q2VlR2Vl?= Microsoft Excel Worksheet Functions 5 13th Sep 2007 06:22 PM
Reverse Legend order when select "Categories in reverse order" =?Utf-8?B?TWNqYW5l?= Microsoft Powerpoint 1 18th Mar 2006 07:23 PM
excel vba - selection.clearcontents and how to reverse chief Microsoft Excel Misc 8 15th Apr 2004 04:04 PM
Matrix Reverse with row identification to reverse bas PJ Karaffa Microsoft Excel Worksheet Functions 4 3rd Feb 2004 06:19 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:47 AM.