Reverse words based on condition in excel?

|Cell A                     |    Cell B                  |Cell C         |
|---------------------------|----------------------------|---------------|
|Thomas Reynaud best friend |best friend Thomas Reynaud  |Name           |
|---------------------------|----------------------------|---------------|
|Red Pink Blue Cube         |Cube Pink Blue Red          |               |
|---------------------------|----------------------------|---------------|
|start text end here        |text start end here         |Text           |
|--------------------------------------------------------|---------------|

I have excel sheet with data i want to reverse words Based on conditions as i given example above in column A i have data which i need to reverse in column B based on Column C. If column C contain name in value then column A test reverse like Column B. If column C Contain empty cell then Column a value change like column B as show in my example sheet above row 2. If contain other value in column C it reverse the Column A's value like result in Column B in row 3 above.

I tried many formulas but they all work same not based on condition. mainly i tried this formula

=MID(I2&" "&I2,FIND(" ",I2)+1,LEN(I2))

and more similarly this one. Hope you understand my question thanks.

Originally Posted by YasserKhalil

May be

Sub Test()
    Dim arr, X, I As Long

    arr = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
        For I = 1 To UBound(arr, 1)
            X = Split(arr(I, 1), " ")
            If arr(I, 3) = "Name" Then
                arr(I, 2) = X(2) & " " & X(3) & " " & X(0) & " " & X(1)
            ElseIf arr(I, 3) = "" Then
                arr(I, 2) = X(3) & " " & X(1) & " " & X(2) & " " & X(0)
            ElseIf arr(I, 3) = "Text" Then
                arr(I, 2) = X(1) & " " & X(0) & " " & X(2) & " " & X(3)
            End If
        Next I
    Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value = arr
End Sub

thanks dear its work great . thanks for sharing your knowledge .

Originally Posted by YasserKhalil

You're welcome. Glad I can offer some help
Please mark the thread as solved
Regards

hi i got error some time "Run time error 9" subscript out of range any help in this why i got this error some time .

so what can do for this so it work with if their is dont have 4 words ? any idea , thanks

If you don't have 4 words in Col.A, it will give you an error.

if column have less then 4 words and its name then no reverse and if have less then 4 words and its text reverse words with each other . thanks

You showed us a clear logic for 4 words, now you need to show us a clear logic for others.
2, 3 ,5, 6, 7 words. etc.

Originally Posted by YasserKhalil

I relied on the snapshot in first post and it was clear there are four words. Can you give more examples and the logic you intend to follow ?

i tired and it work sometime even if cell have 3 words .. dont know how but its work .

Originally Posted by notbanme

i tired and it work sometime even if cell have 3 words .. dont know how but its work .

i got it how work even if cell have value 3 word because it have space include so its 4 .

is this possible this code add space at the end of values i mean if cell have 3 words code add space at the end of words and make it 4 . and if cell have 2 words it add 2 spaces so make it 4 . thanks