Move first word to the end
This tutorial shows how to move the first word to the end of the string through the use of an Excel formula, with the RIGHT, LEN, FIND and LEFT functions, or VBA
Hard coded formula
Cell reference formula
|
|
GENERIC FORMULA
=RIGHT(string,LEN(string)-FIND(" ",string))&" "&LEFT(string,FIND(" ",string)-1)
ARGUMENTS GENERIC FORMULA
=RIGHT(string,LEN(string)-FIND(" ",string))&" "&LEFT(string,FIND(" ",string)-1)
ARGUMENTS EXPLANATION This formula uses the RIGHT, LEN, FIND and LEFT functions to move the first word to the end 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 first word to the end entered directly in the formula or referenced to a cell.
In this example we are moving the word 'bread', which is the first word in cell B5, to the end of the cell. |
Hard coded against single cell
Sub Move_first_word_to_the_end()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'move the first word in a string to the end
ws.Range("C5") = Right("bread butter milk", Len("bread butter milk") - InStr("bread butter milk", " ")) & " " & Left("bread butter milk", InStr("bread butter milk", " ") - 1)
ws.Range("C5") = Right("bread butter milk", Len("bread butter milk") - InStr("bread butter milk", " ")) & " " & Left("bread butter milk", InStr("bread butter milk", " ") - 1)
End Sub
Cell reference against single cell
Sub Move_first_word_to_the_end()
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Analysis")
Set rng = ws.Range("B5")
Set rng = ws.Range("B5")
'move the first word in a string to the end
ws.Range("C5") = Right(rng, Len(rng) - InStr(rng, " ")) & " " & Left(rng, InStr(rng, " ") - 1)
ws.Range("C5") = Right(rng, Len(rng) - InStr(rng, " ")) & " " & Left(rng, InStr(rng, " ") - 1)
End Sub
Hard coded against range of cells
Sub Move_first_word_to_the_end()
'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 first word in a string to the end
For i = 0 To 3
strStringReturn = strString(i)
x = 5
x = x + i
ws.Range("C" & x) = Right(strStringReturn, Len(strStringReturn) - InStr(strStringReturn, " ")) & " " & Left(strStringReturn, InStr(strStringReturn, " ") - 1)
x = 5
x = x + i
ws.Range("C" & x) = Right(strStringReturn, Len(strStringReturn) - InStr(strStringReturn, " ")) & " " & Left(strStringReturn, InStr(strStringReturn, " ") - 1)
Next i
End Sub
Cell reference against range of cells
Sub Move_first_word_to_the_end()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'move the first word in a string to the end
For i = 5 To 8
ws.Range("C" & i) = Right(ws.Range("B" & i), Len(ws.Range("B" & i)) - InStr(ws.Range("B" & i), " ")) & " " & Left(ws.Range("B" & i), InStr(ws.Range("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 |
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 | |
| FIND Function | The Excel FIND function returns the position of a specific sub-string within a string |