manipulate string

[daveyc18]

hi...

trying to make this

Lindros Luxur Stairway (11122412)

[TABLE="width: 412"]
<tbody>[TR]
[TD="class: xl94, width: 412, align: left"][/TD]
[/TR]
</tbody>[/TABLE]

into a different arrangement


11122412- Lindros Luxur Stairway

...

can somebody help use a formula to do this ? the number of digits will always be the same (eight), but the text will vary.

 

[Rick Rothstein]

Give this formula a try...

=SUBSTITUTE(MID(A1&A1,FIND("(",A1)+1,LEN(A1)-2),")"," - ")

 

[sandy666]

alternative

PowerQuery <==> Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitAndReplace = Table.ReplaceValue(Table.SplitColumn(Source, "raw", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"raw.1", "raw.2"}),")","",Replacer.ReplaceText,{"raw.2"}),
    Merge = Table.CombineColumns(SplitAndReplace,{"raw.2", "raw.1"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"result")
in
    Merge[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Lindros Luxur Stairway (11122412)[/td][td][/td][td=bgcolor:#E2EFDA]11122412 - Lindros Luxur Stairway[/td][/tr]
[/table]