it moves the first part after two items
Len(ini) + 2, This doesn't mean 2 elements, it means 2 spaces.
I'll try to explain what these 2 lines of the macro do:
VBA Code:
ini = Split(c.Value, " ")(0)
c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini
Taking this as an example:
FR BANANA QQ MM N/L KK
c.Value = "FR BANANA QQ MM N/L KK"
Split(c.Value, " ")
Separate each word, so we have:
Item
(0) FR
Item(1) BANANA
Item(2) QQ
Item(3) MM
Item(4) N/L
Item(5) KK
ini = Split(c.Value, " ")
(0)
Then
ini = "FR"
Len(ini)
Length of the content of the ini variable
len("FR")
length = 2
Len(ini) + 2
2 + 2 =
4 (In this operation we have a 4, it does not mean 2 items. Let's pause right now...)
Remember this:
c.value = "FR BANANA QQ MM N/L KK"
If we separate each letter of the text, it would be something like this:
Position 1: "F"
Position 2: "R"
Position 3: " "
Position 4: "B"
Position 5: "A"
Position 6: "N"
etc...
c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini
Do you remember 4?
c.Value = Mid(c.Value,
4) & " " & ini
That means that we are going to extract a part of the text and it is going to start the extraction at position
4.
Then
c.value = Mid("FR BANANA QQ MM N/L KK", 4)
c.value = "BANANA QQ MM N/L KK" & " " & ini
c.value = "BANANA QQ MM N/L KK" & " " & "FR"
Result:
c.value = "BANANA QQ MM N/L KK FR"
----
You have a problem with your data as you have
whitespace before AND after the text:
----
Perhaps the following code tweak will help with any spaces you have at the beginning or end of the text, but you should clean up your data.
VBA Code:
Sub move_first_part_to_last_part()
Dim sh As Worksheet
Dim c As Range
Dim ini As String, cad As String
For Each sh In Sheets
For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
If sh.Range("Z" & c.Row).Value = "" Then
cad = WorksheetFunction.Trim(c.Value)
ini = Split(cad, " ")(0)
c.Value = Mid(cad, Len(ini) + 2) & " " & ini
sh.Range("Z" & c.Row).Value = "x"
End If
Next
Next
End Sub