• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get The Number and Text After Spesific Words

Kenshin

Member
God Bless You All, Need Help Again Fellas, Thank In Advanced


Regards
Kenshin
 

Attachments

  • Book1.xlsx
    10.7 KB · Views: 13
Hi,

In B2:

If you have Office 365:

=TEXT(TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b[preceding::*[1]=""RT""]")),"000")

which will spill to the right automatically.

If not, for Excel 2013 and later:

=IFERROR(TEXT(INDEX(FILTERXML("<a><b>"&SUBSTITUTE($A2," ","</b><b>")&"</b></a>","//b[preceding::*[1]=""RT""]"),COLUMNS($A2:A2)),"000"),"")

which will need manually copying to the right.

Regards
 
@XOR LX
Really cool use of [preceding::*[1] , I learn a new trick again :)

Old style formula
B2
=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,"RT",REPT(" ",200)),COLUMNS($B2:B2)*200,200))," ",REPT(" ",99)),99))

or POWER QUERY

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitList = Table.AddColumn(Source, "RT", each List.Transform(List.Range(Text.Split([Address],"RT"),1),each Text.BeforeDelimiter(Text.Trim(_)," "))),
    Join = Table.TransformColumns(SplitList, {"RT", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split_Column = Table.SplitColumn(Join, "RT", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),List.Max(List.Transform(SplitList[RT],each List.Count(_))) ),
    Removed_Columns = Table.RemoveColumns(Split_Column,{"Address"})
in
    Removed_Columns
 

Attachments

  • Book1.xlsx
    25.7 KB · Views: 3
Back
Top