Reverse a string

Brian from Maui

MrExcel MVP

Joined

Feb 16, 2002

Messages

8,459

• Sep 7, 2006

• #2

Is there a function that can reverse the contents of a cell?

i.e. A1=Cat ...result = tac

Thanks,
Brian

Click to expand...

ASAP Utilities has a function that does just that,

www.asap-utilities.com

found under the Text menu...

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 7, 2006

• #3

Or a User Defined function

Code:

Function reverse(S As String)
    Dim Temp As String, I As Integer, n As Integer
    n = Len(S)
    For I = n To 1 Step -1
        Temp = Temp & Mid(S, I, 1)
    Next I
    reverse = Temp & Right(S, Len(S) - n)
End Function

ASAP-Utilities is better though unless you are going to be doing this a lot.

 

Upvote 0

BrianM

Well-known Member

Joined

Jan 15, 2003

Messages

768

Office Version

1. 2016

Platform

1. Windows

• Sep 7, 2006

• #4

Thanks to you both.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 7, 2006

• #5

Hi,

there is an inbuiltfunction
might be a more recent one

Code:

MsgBox StrReverse("cat")

kind regards,
Erik

 

Upvote 0

DominicB

Well-known Member

Joined

Oct 3, 2005

Messages

1,569

• Sep 7, 2006

• #6

Good evening BrianM

This would do the trick :

Code:

Function Reverse(Backwards As String)
Dim Length, Pos As Integer
Reverse = ""
Length = Len(Backwards)
For Pos = Length To 1 Step -1
Reverse = Reverse & Mid(Backwards, Pos, 1)
Next Pos
End Function

My add-in, available from the link below has this as a function, so once installed =REVERSE(A1) would show tac (if A1 contained "cat")

HTH

DominicB

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 7, 2006

• #7

talking about functions:

Code:

Function reverse(S As String)
    reverse = StrReverse(S)
End Function

so no need to loop
as mentioned, check if it works for your version

 

Upvote 0

Todd Bardoni

Well-known Member

Joined

Aug 29, 2002

Messages

3,042

• Sep 7, 2006

• #8

Using formulas, (not too pretty though):-

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	ToddBardoni		i	n	o	d	r	a	B		d	d	o	T	T	T	T	T	T	T	T	T	T	T	T	T	T	T	inodraBddoT
Sheet1

In C1:AB1

Code:

=LEFT(RIGHT(Sheet1!$A$1,COLUMN(1:$256)),1)

Confirmed w/ Ctrl+Shift+Enter

In AC1

Code:

=LEFT(C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1&U1&V1&W1&X1&Y1&Z1&AA1&AB1,LEN($A$1))

 

Upvote 0

DominicB

Well-known Member

Joined

Oct 3, 2005

Messages

1,569

• Sep 7, 2006

• #9

Hi Erik

That "StrReverse" commands a handy thing to know. It would be interesting to know when it was introduced. As far back as XL2000...?

DominicB

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 7, 2006

• #10

Domonic,

I'll check it out on an elder PC with XL2000.
Almost sure it wasn't available in XL97

best regards,
Erik

 

Upvote 0