I try to write a macro that reverse the following text :


"abc 12/34"

to

"cba 12/34"

i.e., it reverses only the text, not the numbers.

'-------------------------------------
Sub test()
Dim MyString As String
Dim NewString As String
MyString = "abc 12/34"
NewString = Mid(MyString, 3, 1) & Mid(MyString, 2, 1) _
& Mid(MyString, 1, 1) & Right(MyString, 6)
MsgBox (MyString & vbCr & NewString)
End Sub
'------------------------------------------



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I guess I did not explain my self right.

The text is not a constant, but random variable, for example:

a street address.


BrianB, wrote in message ...
'-------------------------------------
Sub test()
Dim MyString As String
Dim NewString As String
MyString = "abc 12/34"
NewString = Mid(MyString, 3, 1) & Mid(MyString, 2, 1) _
& Mid(MyString, 1, 1) & Right(MyString, 6)
MsgBox (MyString & vbCr & NewString)
End Sub
'------------------------------------------



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

One way is to look for that space and then reverse it based on where you found
it.

Option Explicit
Sub testme()
Dim myCell As Range
Dim SpacePosition As Long

For Each myCell In Selection.Cells
SpacePosition = InStr(1, myCell.Value, " ")
If SpacePosition 0 Then
myCell.Value = Mid(myCell.Value, SpacePosition + 1) _
& " " & Left(myCell.Value, SpacePosition - 1)
End If
Next myCell

End Sub

Select a range and try it out.

don't select cells that are formulas--you'll lose them if you run this.


levyta wrote:

I guess I did not explain my self right.

The text is not a constant, but random variable, for example:

a street address.

BrianB, wrote in message ...
'-------------------------------------
Sub test()
Dim MyString As String
Dim NewString As String
MyString = "abc 12/34"
NewString = Mid(MyString, 3, 1) & Mid(MyString, 2, 1) _
& Mid(MyString, 1, 1) & Right(MyString, 6)
MsgBox (MyString & vbCr & NewString)
End Sub
'------------------------------------------



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

the macro does not do what I hoped for.

suppose we have:

abc 12/34 efg

the macro gives:

12/34 efg abc


and not:

cba 12/34 gfe


hope some one can help me.



Dave Peterson wrote in message ...
One way is to look for that space and then reverse it based on where you found
it.

Option Explicit
Sub testme()
Dim myCell As Range
Dim SpacePosition As Long

For Each myCell In Selection.Cells
SpacePosition = InStr(1, myCell.Value, " ")
If SpacePosition 0 Then
myCell.Value = Mid(myCell.Value, SpacePosition + 1) _
& " " & Left(myCell.Value, SpacePosition - 1)
End If
Next myCell

End Sub

Select a range and try it out.

don't select cells that are formulas--you'll lose them if you run this.


levyta wrote:

I guess I did not explain my self right.

The text is not a constant, but random variable, for example:

a street address.

BrianB, wrote in message ...
'-------------------------------------
Sub test()
Dim MyString As String
Dim NewString As String
MyString = "abc 12/34"
NewString = Mid(MyString, 3, 1) & Mid(MyString, 2, 1) _
& Mid(MyString, 1, 1) & Right(MyString, 6)
MsgBox (MyString & vbCr & NewString)
End Sub
'------------------------------------------



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/