Previous Topic Next Topic

Last word of phrase becomes first word

Closed
Jeni - Oct 2, 2010 at 02:28 PM
 Jeni - Oct 7, 2010 at 10:29 AM
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
Related:

5 responses

Answer 1 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 2, 2010 at 10:54 PM
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
Jeni
Oct 3, 2010 at 01:22 PM
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!!
Answer 2 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 3, 2010 at 09:41 PM
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
Jeni
Oct 4, 2010 at 09:21 AM
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.
Answer 3 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 4, 2010 at 09:00 PM
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.
Jeni
Oct 6, 2010 at 12:14 PM
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.
Answer 4 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 7, 2010 at 06:08 AM
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
Answer 5 / 5
Jeni
Oct 7, 2010 at 10:29 AM
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?