Move last word to the front
This tutorial shows how to move the last word in a string to the end through the use of an Excel formula, with the TRIM, RIGHT, SUBSTITUTE, REPT, LEN and LEFT functions, or VBA
EXCEL FORMULA 1. Move last word to the front using the TRIM, RIGHT, SUBSTITUTE, REPT, LEN and LEFT functions
EXCEL
Hard coded formula
Cell reference formula
|
|
GENERIC FORMULA
=TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",LEN(string))),LEN(string)))&" "&LEFT(string,LEN(string)-LEN(TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",LEN(string))),LEN(string))))-1)
ARGUMENTS GENERIC FORMULA
=TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",LEN(string))),LEN(string)))&" "&LEFT(string,LEN(string)-LEN(TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",LEN(string))),LEN(string))))-1)
ARGUMENTS EXPLANATION This formula uses the RIM, RIGHT, SUBSTITUTE, REPT, LEN and LEFT functions to move the last word to the start of a string.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the string in which you want to move the last word in a string to the front entered directly in the formula or referenced to a cell.
In this example we are moving the word 'milk', which is the last word in cell B5, to the front of the cell. |
Hard coded against single cell
Sub Move_last_word_to_the_front()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'move the last word in a string to the front
ws.Range("C5").Formula = "=TRIM(RIGHT(SUBSTITUTE(""bread butter milk"","" "",REPT("" "",LEN(""bread butter milk""))),LEN(""bread butter milk"")))&"" ""&LEFT(""bread butter milk"",LEN(""bread butter milk"")-LEN(TRIM(RIGHT(SUBSTITUTE(""bread butter milk"","" "",REPT("" "",LEN(""bread butter milk""))),LEN(""bread butter milk""))))-1)"
ws.Range("C5").Formula = "=TRIM(RIGHT(SUBSTITUTE(""bread butter milk"","" "",REPT("" "",LEN(""bread butter milk""))),LEN(""bread butter milk"")))&"" ""&LEFT(""bread butter milk"",LEN(""bread butter milk"")-LEN(TRIM(RIGHT(SUBSTITUTE(""bread butter milk"","" "",REPT("" "",LEN(""bread butter milk""))),LEN(""bread butter milk""))))-1)"
End Sub
Cell reference against single cell
Sub Move_last_word_to_the_front()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'move the last word in a string to the front
ws.Range("C5").Formula = "=TRIM(RIGHT(SUBSTITUTE(B5,"" "",REPT("" "",LEN(B5))),LEN(B5)))&"" ""&LEFT(B5,LEN(B5)-LEN(TRIM(RIGHT(SUBSTITUTE(B5,"" "",REPT("" "",LEN(B5))),LEN(B5))))-1)"
ws.Range("C5").Formula = "=TRIM(RIGHT(SUBSTITUTE(B5,"" "",REPT("" "",LEN(B5))),LEN(B5)))&"" ""&LEFT(B5,LEN(B5)-LEN(TRIM(RIGHT(SUBSTITUTE(B5,"" "",REPT("" "",LEN(B5))),LEN(B5))))-1)"
End Sub
Hard coded against range of cells
Sub Move_last_word_to_the_front()
'declare variables
Dim ws As Worksheet
Dim strString(4) As String
Dim ws As Worksheet
Dim strString(4) As String
Set ws = Worksheets("Analysis")
strString(0) = "bread butter milk"
strString(1) = "milk butter bread apple"
strString(2) = "apple milk butter bread"
strString(3) = "butter apple milk"
strString(1) = "milk butter bread apple"
strString(2) = "apple milk butter bread"
strString(3) = "butter apple milk"
'move the last word in a string to the front
For i = 0 To 3
strStringReturn = strString(i)
x = 5
x = x + i
ws.Range("C" & x).Formula = "=TRIM(RIGHT(SUBSTITUTE(""" & strStringReturn & ""","" "",REPT("" "",LEN(""" & strStringReturn & """))),LEN(""" & strStringReturn & """)))&"" ""&LEFT(""" & strStringReturn & """,LEN(""" & strStringReturn & """)-LEN(TRIM(RIGHT(SUBSTITUTE(""" & strStringReturn & ""","" "",REPT("" "",LEN(""" & strStringReturn & """))),LEN(""" & strStringReturn & """))))-1)"
x = 5
x = x + i
ws.Range("C" & x).Formula = "=TRIM(RIGHT(SUBSTITUTE(""" & strStringReturn & ""","" "",REPT("" "",LEN(""" & strStringReturn & """))),LEN(""" & strStringReturn & """)))&"" ""&LEFT(""" & strStringReturn & """,LEN(""" & strStringReturn & """)-LEN(TRIM(RIGHT(SUBSTITUTE(""" & strStringReturn & ""","" "",REPT("" "",LEN(""" & strStringReturn & """))),LEN(""" & strStringReturn & """))))-1)"
Next i
End Sub
Cell reference against range of cells
Sub Move_last_word_to_the_front()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'move the last word in a string to the front
For i = 5 To 8
ws.Range("C" & i).Formula = "=TRIM(RIGHT(SUBSTITUTE(B" & i & ","" "",REPT("" "",LEN(B" & i & "))),LEN(B" & i & ")))&"" ""&LEFT(B" & i & ",LEN(B" & i & ")-LEN(TRIM(RIGHT(SUBSTITUTE(B" & i & ","" "",REPT("" "",LEN(B" & i & "))),LEN(B" & i & "))))-1)"
Next i
End Sub
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first word from a string | How to return the first word from a string through the use of an Excel formula or VBA | |
| Move first word to the end | How to move the first word to the end of the string through the use of an Excel formula or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| LEFT Function | The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side | |
| RIGHT Function | The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string | |
| TRIM Function | The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string |