String to Rev-string in Excel or VBA Code

[basavarajkh]

Hi

How do I change String to Rev-String

My Question is

for ex: ABCD#?1234

I wanted it's in reverse see 4321?#DCBA like that.

which formula can I use.

I want both formula & VBA code also.

Plz help me


Thank in Advance


Basavaraj K H

Member of Chandoo.org

 

[magbo]

As Excel text formulas don't accept array argument, VBA function StrReverse(text) seems to be the solution. For instance sth like this:

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


Btw. have you tried to ask Google? It knows some 5,700,000 answers for "excel string reverse"

 

[asa]

magbo, I'm not an expert with some of the high-tech formulas, but could this be adapted to a single-cell formula?

[pre]

{=if(ROW($1:$256)>len(a1),"",MID(a1,LEN(a1)+1-ROW($1:$256),1))}

[/pre]
This array formula if entered in a vertical range (or horizontal if you add TRANSPOSE) of cells will extract each character from A1 in reverse order, 1 character per cell.

 

[magbo]

Hi Asa

Everytime I tried to use CONCATENATE as an array formula, it didn't work (Ex 2003). For instance array formula "=CONCATENATE(A1:A4)" does not display an error, but result is only text from A1.

Your impressive formula works great, but the problem is in combining values from array into string. User VBA function with just one command is so much easier...

 

[asa]

I got the ROW() trick from http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba


after checking the 5.7 mil + google results you mentioned


That link is actually about a formula to retrieve the last word in a sentence. It was an interesting problem in and of itself.. and there are several working formulas to do it in the comments on that page.


There's no reason to use a bizarre, complicated, and possibly slow formula in instances when a simple VBA function works great.


Concatenate() doesn't take arrays in Excel 2010 either... for no good reason


I just tried {=StringConcat("",if(ROW($1:$256)>len(a1),"",MID(a1,LEN(a1)+1-ROW($1:$256),1)))}, out of curiosity, and yes indeed it does work! it's not VBA-free by a longshot but interesting nonetheless.


Asa

 

[asa]

I shortened my pointless version ..

[pre]

=StringConcat("",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

[/pre]
It will also be more efficient since it won't build an array any larger than the source text (or any smaller, as the old version was limited to 256 characters).

 

[magbo]

Asa, great work!

On the other hand - it hard to imagine reason why the CONCATENATE was not improved...

 

[basavarajkh]

Thank you for your information.

To add the fuel to arguments, please let me know about - in the V B Editor is't possible to get the string to rev string by going through "V B Editor>Insert>user form>Textbox1 & Textbox2"?

for same above e.g. - ABCD#?1234 to 4321?#DCBA.


There I should get rev string of box1 in box2.


Please help me out in writing the code in V B for this.


Regards

Basavaraj K H

 

[magbo]

Basavaraj, what exactly do you need?

Do you already have a Userform with textboxes? Do you want to modify one of them? Or are you planning to create a new userform? Or do you need to add some code to your project? Do you need more than:

Textbox2.text = StrReverse(Textbox1.text)