Swap last and first word in a sentence in an excel column

[sanban]

Hello Everyone -

I hope you guys are having a great time.

I am working on a report that pulls the customer information with all the other details. The challenge is the customer name information is not as per the desired order.
In the original report, the name information is displayed in this fashion 'Van Jean Claude Mr'.

Is there a script or function in excel to swap the last word and first word places in a sentence. In my report, I would want the customer name to be displayed as 'Mr Jean Claude Van'. Appreciate if someone can help me in this regard.

Many thanks.

Kind regards,
San

[SamT]

Function First2Last(Input As String)As STring
Dim Tmp1, tmp2 As String, i as long
Tmp = Split(Input, " ")
For i = Lbound(tmp1) + 1 to UBound(Tmp1) - 1
tmp2 = tmp2 & Tmp1(i)
Next i

First2Last = Tmp1(Ubound(tmp1)) & Tmp2 & Tmp1(Lbound(tmp1))
End Function

Y0u can also use Instr(Input, " ") and InStrRev(Input, " "), then use Right, Mid, and Left with some basic math

[Paul_Hossler]

One way




Option Explicit


Sub drv()
Dim s As String

s = " Van Jean Claude Mr"


MsgBox SwapFirstLast(s)


End Sub


Function SwapFirstLast(s As String) As String
Dim v As Variant
Dim x As String

v = Split(s, " ")
x = v(LBound(v))
v(0) = v(UBound(v))
v(UBound(v)) = x

SwapFirstLast = Join(v, " ")


End Function

[sanban]

Hi Sam, thanks for your response. I know, you would want the student to do their homework but unfortunately I have zero knowledge in VBA scripting. And the script you have in there is throwing error while compiling - (first and third line). Not sure where I am going wrong.

[sanban]

Hi Paul, I really appreciate your time n effort in coming up with a script. I can see that you've hardcoded the input value. Please note that I have to do this for 1000+ records. Is there a way we can add a variable component to the script rather than passing a fixed value?

Cheers,
San

One way




Option Explicit


Sub drv()
Dim s As String

s = " Van Jean Claude Mr"


MsgBox SwapFirstLast(s)


End Sub


Function SwapFirstLast(s As String) As String
Dim v As Variant
Dim x As String

v = Split(s, " ")
x = v(LBound(v))
v(0) = v(UBound(v))
v(UBound(v)) = x

SwapFirstLast = Join(v, " ")


End Function

[jolivanes]

Just for the heck of it. As SamT suggested.

Sub Maybe()
Dim c As Range, i As Long, j As Long
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
i = InStr(c, " ")
j = InStrRev(c, " ")
c.Value = Mid(c, j) & Mid(c, i, (Len(c) - i - (Len(c) - j))) & " " & Left(c, i - 1)
Next c
End Sub

[Paul_Hossler]

Hi Paul, I really appreciate your time n effort in coming up with a script. I can see that you've hardcoded the input value. Please note that I have to do this for 1000+ records. Is there a way we can add a variable component to the script rather than passing a fixed value?

Cheers,
San

Not tested



Sub drv()
Dim r As Range

For Each r in Range("A1:A10000").Cells
r.Value = SwapFirstLast(r.Value)
Next


End Sub

[SamT]

It seems that "Input" is a reserved keyword.
I fixed that, then tested and tweaked it a bit to perfect the output (added spacing)


Function First2Last(strInput As String) As String
Dim Tmp1, tmp2 As String, i As Long
Tmp1 = Split(strInput, " ")
For i = LBound(Tmp1) + 1 To UBound(Tmp1) - 1
tmp2 = tmp2 & Tmp1(i) & " "
Next i

First2Last = Tmp1(UBound(Tmp1)) & " " & tmp2 & Tmp1(LBound(Tmp1))
End Function


'Insure Immediate window is open by pressing Ctrl+G
Sub test_First2Last()
Dim Y
Const X = "Van Jean Claude Mr"
Y = First2Last(X)

Debug.Print Y 'Y will equal "Mr Jean Claude Van"
End Sub