Using only Excel to reverse the contents of the cell.

all4excel · 12-20-2007, 02:19 PM

Using Excel to reverse the contents of the cell.

I want to reverse the contents of the cell be it a Number or Test.

Lets say iF i have a number like 12345
I want it to display as 54321

If I have text like "Alphabets " -- I want it to display as "stebahplA".

I want this to be executed only in Excel not by using VBA or Macros...

Is there any logic?

WE need to know the Length of the cell contents
Len()

Mod ()

Then what?

How do i loop till the number is over?

**davesexcel** · 12-20-2007, 03:49 PM

Here's a UDF

http://www.ozgrid.com/VBA/ReverseCell.htm

all4excel · 12-21-2007, 02:30 AM

Originally Posted by davesexcel

Here's a UDF

http://www.ozgrid.com/VBA/ReverseCell.htm

This link is useful however i want to get the result only using Excel.

Is that possible?

Thanx for the solution anyways...

**mikerickson** · 12-21-2007, 03:26 AM

If you have your text in A1

In B1, put
=RIGHT($A$1,1)

In B2, put
=B1&IF(ROW()<=LEN($A$1),MID($A$1,LEN($A$1)-ROW()+1,1),"")
and fill down.

Then in any cell,
=INDEX(B:B,LEN(A1),1)
will be the reverse of A1

Cell B(Len(A1)) will also have the reverse of A1.
eg. if A1="abc" then B3="cba" and INDEX(B:B,LEN(A1),1) = "cba"

LATER edit:

Another approach.
In B1, put
=B2&IF(ROWS($A1:$A$20)>LEN($A$1),"",MID($A$1,LEN($A$1)-(20-ROWS($A$1:A1)),1))
and fill down.
B1 will contain the reverse of A1, but only if the formula has been filled down 20 rows.

all4excel · 12-21-2007, 05:24 AM

Originally Posted by mikerickson

If you have your text in A1

In B1, put
=RIGHT($A$1,1)

In B2, put
=B1&IF(ROW()<=LEN($A$1),MID($A$1,LEN($A$1)-ROW()+1,1),"")
and fill down.

Then in any cell,
=INDEX(B:B,LEN(A1),1)
will be the reverse of A1

Cell B(Len(A1)) will also have the reverse of A1.
eg. if A1="abc" then B3="cba" and INDEX(B:B,LEN(A1),1) = "cba"

LATER edit:

Another approach.
In B1, put
=B2&IF(ROWS($A1:$A$20)>LEN($A$1),"",MID($A$1,LEN($A$1)-(20-ROWS($A$1:A1)),1))
and fill down.
B1 will contain the reverse of A1, but only if the formula has been filled down 20 rows.

It does work but is possible to have it directly in one cell without using two-three cells...?

**daddylonglegs** · 12-21-2007, 06:31 AM

Don't know if you are interested in add-ins but Morefunc add-in has a REVERSETEXT function....

http://xcell05.free.fr/morefunc/english/

....alternatively, in a single cell for up to 10 characters...

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

Extend as necessary

Obviously this isn't a very good approach if you have a large amount of text to reverse, but I don't think there's another formula alternative, the problem being that you can't concatenate an array with native excel functions, so you have to concatenate formulas together like the above

**martindwilson** · 12-21-2007, 07:53 AM

coz i was bored here's dll's aproach extended to 50 characters

Please Login or Register  to view this content.

all4excel · 12-21-2007, 09:00 AM

I was actually thinking there would be some way maybe which i had not figured out...!

But theres nothing actually as you Experienced people have proved to me...

Thanks for the explanation DADdylonglegs abt the native formula and concatenate function....