• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reversing a Text using Formula

P

pramoth.u

New Member
Hi,

I have a text called Ibrahimpatnam in need to reverse this text as mantapmiharbi using a formula.Please suggest me the formulas to derive the solution.


Ps:No macros please.
 
I am not sure..but its almost impossible thorugh formula only...


You can use this

http://www.ozgrid.com/VBA/ReverseCell.htm
 
Or otherwise do we have formula to reverse a line of text as like below


Orginal text:Third Floor Rajwada Chowk


Change to:Chowk rajwada floor third.


Will that be possible with formula or do we need to go for macro?...kindly suggest


Note:I just given the sample line,it has characters more than 300+ in each columns.I need to apply the logic in all columns for conversion.
 
Swapping whole words around would be even more difficult. =(

Using a UDF would make things much easier...
 
I would suggest you first break all the words into different cells using Data - text to columns and then concatenate the reverse order.....
 
Hi Pramoth ,


As Luke has suggested , a UDF can be used as a formula within your worksheet ; so what exactly is your restriction that you cannot use macros ?


For example , if a macro called Reverse is written to reverse your input string , then within your worksheet if cell A1 contains the string "Ibrahimpatnam" , then in another cell you can use the 'formula' =Reverse(A1) and get the string "mantapmiharbI".


Narayan
 
I just want to chime in that to my knowledge as well, a formula alone cannot reverse a string either character by character or word by word.


If you are willing to use a macro, I think this one is better than the OzGrid one - this one uses built-in VBA command to reverse a string:

http://www.bettersolutions.com/excel/EIK284/LR521811611.htm


If you want to know what a formula to reverse a string would look like,

Here's one that depends on a UDF/macro. The macro is called StringConcat and stands in for the CONCATENATE function, but adds the missing feature of working on arrays (which is needed, I think, to achieve your goal). Replace A1 with the cell containing the text to reverse.

Code: 
=StringConcat("",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))
It needs to be entered as an array formula (ctrl-shift-enter)


Also, to try it you would have to be willing to download and import or paste in the StringConcat macro, which can be found at http://www.cpearson.com/excel/stringconcatenation.aspx


This method is just a demonstration of what a near-native Excel formula would be. It works, but the first macro linked is better.


The formula for the first linked UDF/macro would be:

=REVERSE(A1)
To reverse your text word-by-word would require modifications to the above... but still depend on a macro behind the scenes, sorry.


Asa


edit:

Instead of saying "a formula alone cannot reverse a string" I should say "a 'reasonable' formula alone..." As far as I know. A very long formula could be devised, but depending on your version of Excel, and the length of the text you need to handle, might not work (then you could split it out to many cells I suppose).
 
Hi Pramoth,


if you dont mind and if it is not confidential, would you share what you are trying achieve by swapping words position or reversing the text?


I am curious to know where we apply this technique?


Regards,

Prasad
 
You must log in or register to reply here.
Back
Top