Last word of phrase becomes first word

[Closed]
Report
-
 Jeni -
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

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
This kind of worked: after I run the macro SOME of the phrases from column A are returned in column B with the last word in A as the first word in B. But I'm getting a run time error, it says: run time error '5' Invalid procedure call or argument at x = x & " " & Mid(c, 1, k-1). I think I'm getting this error becasue i also have cells that contain only one word (no spaces). What should i add to the macro to account for cells that only contain one word - the same word that appears in A should appear the same in B if there are no spaces present.

Thanks again!!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
I'm still getting the same error messege. Could this be because some of the cells that contain one word also contain hyphens? For example a cell contains the word ACP-CE and thats where the macro stops running and returns the error message - although, now all other one word cells ran with this modified macro.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
This code only catches the fist cell in A that contains one word and returns that same word in cell B. But then i keep getting errors once the macro reaches another one word cell in A.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
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?