last word of phrase becomes first wordClosed

Question

Hi, 

I'm having difficulty writing a macro in excel. What I want to do is find the last word in a phrase and move it to the front of the phrase. None of the phrases end in a period so I'm trying to find the last occuring space and move all letters that come after that space to the front of the phrase - the space that is left, after the last word is moved, has to be deleted. Also, all phrases are unique; for example some phrases have 2 words, some have 3, some have 4 and so on. All phrases are in colum A.

This is an example of what the phrase is now and what it should be after the macro is run:
ColumA
I need Help
I used a Macro
Please help me


ColumnB(after macro is run)
Help I need
Macro I used a
me Please help

Thanks so much for the help

venkat1926 · Answer

there are many ways to do it..  run this macro. the data will be like this with a heading in A1


heading
I  need Help 
I used a Macro 
Please help me 


the macro is 


Sub test()
Dim j As Long, x As String, k As Long
Dim r As Range, c As Range
Columns("B:B").Delete
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
j = Len(c)
For k = j To 1 Step -1
'msgbox Mid(c, k, 1)
If Mid(c, k, 1) = " " Then GoTo line1
Next k
line1:
x = Right(c, Len(c) - k)
'msgbox x
x = x & " " & Mid(c, 1, k - 1)
'msgbox x
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = x
Next c
End Sub

venkat1926 · Answer

see whether this modified macro will work?



Sub test()
Dim j As Long, x As String, k As Long
Dim r As Range, c As Range
Columns("B:B").Delete
On Error GoTo line2
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
j = Len(c)
For k = j To 1 Step -1
'msgbox Mid(c, k, 1)
If Mid(c, k, 1) = " " Then GoTo line1

Next k
line1:
x = Right(c, Len(c) - k)
'msgbox x
x = x & " " & Mid(c, 1, k - 1)
'msgbox x
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = x
GoTo nextc
line2:
c.Offset(0, 1) = c
nextc:

Next c
End Sub

venkat1926 · Answer

inadvertently you might have added a space at the end or some other mistake;. You CLEAR(NOT MERELY DELETE)-EDIT-CLEARALL   of the one word cells. 

however I am sending my sample file which you can download from the following web page

http://www.speedyshare.com/files/24550414/jeni_101004.xls

the macro is already in the vb editor of this file.you run the macro and see.

venkat1926 · Answer

see whether this modified macro helps


Sub test()
Dim j As Long, x As String, k As Long
Dim r As Range, c As Range
Columns("B:B").Delete
On Error GoTo line2

Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
If InStr(c, " ") = 0 Then GoTo line2
j = Len(c)
For k = j To 1 Step -1
'MsgBox Mid(c, k, 1)
If Mid(c, k, 1) = " " Then GoTo line1
Next k
line1:
x = Right(c, Len(c) - k)
'MsgBox x
x = x & " " & Mid(c, 1, k - 1)
'MsgBox x
c.Offset(0, 1) = x
GoTo nextc
line2:
c.Offset(0, 1) = c
nextc:

Next c


End Sub

Jeni · Answer

One last question but it may require a new macro, im not sure. Is it possible to rearrange the order of the phrases within the exact same cell? BTW the rarranged phrases will then be used in a UDF so I'm guessing the macro would have to be Public Sub... right?

Last word of phrase becomes first word

5 responses

Similar discussions

Subscribe To Our Newsletter!