Text formula - reverse word

[Kamolga]

Hi,

I face an issue with imported text over a few hundreds lines:

One cell (A1) is like this

Belgium BE » Flanders VLG » Antwerp Province VAN » Arrondissement of Antwerp 11 » Antwerpen 11002

and I wish to have

 Antwerpen (11002), [COLOR=#222222][FONT=Verdana]Arrondissement of Antwerp (11), [/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]Antwerp Province (VAN), [/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]Flanders (VLG), Belgium (BE)[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]

So far I have succeeded in getting

Belgium BE,Flanders VLG,Antwerp Province VAN,Arrondissement of Antwerp 11,Antwerpen 11002

through

=SUBSTITUTE(A1," » ",",")

but how can I reverse it? ​

​

​

 

[sandy666]

here is with PowerQuery (Get&Transform) but I think this is not representative example, anyway:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Belgium BE » Flanders VLG » Antwerp Province VAN » Arrondissement of Antwerp 11 » Antwerpen 11002[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Antwerpen (11002), Arrondissement of Antwerp (11), Antwerp Province (VAN), Flanders (VLG), Belgium (BE)[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"src", Splitter.SplitTextByDelimiter(" » ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "src"),
    ExtractAfter = Table.AddColumn(Split, "Text After Delimiter", each Text.AfterDelimiter([src], " ", {0, RelativePosition.FromEnd}), type text),
    ExtractBefore = Table.TransformColumns(ExtractAfter, {{"src", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    Prefix = Table.TransformColumns(ExtractBefore, {{"Text After Delimiter", each "(" & _, type text}}),
    Suffix = Table.TransformColumns(Prefix, {{"Text After Delimiter", each _ & ")", type text}}),
    Merge = Table.CombineColumns(Suffix,{"src", "Text After Delimiter"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Reverse = Table.ReverseRows(Merge),
    Transpose = Table.Transpose(Reverse),
    Result = Table.CombineColumns(Transpose,{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Result")
in
    Result[/SIZE]

 

[Kamolga]

This is finally how I did it:
Since the number of parts was variable, I had more ifs that I could handle. Instead I used VBA to split my string at each "

» " into an array. With Ubound I can easily know how many parts I have (n in follow case) and reverse them.
Then I split each part of the array into a left and a right string (the right to be placed into parenthesis).
I end wit a function :

Public Function RevText(Rng As Range) As String
[/FONT][/COLOR][COLOR=#006400][FONT=monospace]'Split input[/FONT][/COLOR][COLOR=#333333][FONT=monospace]
Dim iStr As String: iStr = Trim(Rng)
Dim iArray() As String: iArray = Split(iStr, " [COLOR=#333333][FONT=monospace]» [/FONT][/COLOR]")
'How many parts?
Dim n As Long: n = UBound(iArray)
[/FONT][/COLOR][COLOR=#006400][FONT=monospace]'Output (left and right text of each string)[/FONT][/COLOR][COLOR=#333333][FONT=monospace]
Dim lStr, rStr As String
Dim j As Long
    For j = n To 0 Step -1
     lStr = Left(iArray(j), InStrRev(iArray(j), " "))
     rStr = Right(iArray(j), Len(iArray(j)) - (InStrRev(iArray(j), " ")))
        If j = n Then
           RevText = lStr & "(" & rStr & ")"
          Else
           RevText = RevText & "," & lStr & "(" & rStr & ")"
        End If
    Next j
End Function
[/FONT][/COLOR]

and so =RevText(A1) gives me the result I was looking for.

​

 

[Snakehips]

I'm a little late but having done it, I'll post it...

Public Function ReverseTxt(OrigTxt As Range) As String
Dim MyArray
Dim MyArray2
MyArray = Split(Trim(OrigTxt), " » ")
u = UBound(MyArray)
For i = 0 To u
MyArray2 = Split(MyArray(i), " ")
u2 = UBound(MyArray2)
MyArray2(u2) = "(" & MyArray2(u2) & ")"
MyArray(i) = Join(MyArray2, " ")
Next i
ReDim MyArray2(0 To u)
For i = 0 To u
MyArray2(i) = MyArray(u - i)
Next i
ReverseTxt = Join(MyArray2, ", ")
End Function