Custom Text Formula Columns

[leewayn1]

Hello all,

I am new to Power Query and was wondering if there was a similar Power Query syntax/formula as excel.

I have the following string of information:

Jon.Mathan (312345) » Video (893481) » Gallery (1239814)
Dog.day (251654) » video_preroll (23918475)
MobileApps (new ad unit) (45917839) » en-CA (2938754) » news (1924785)

I want to get the middle text from each of these so I get

Video
video_preroll
en-CA

I also want to get the end text:

Gallery
"blank"
news

I used the following two formulas in excel to complete:

=IFERROR(TRIM(LEFT(REPLACE(B2,1,FIND("» ",B2),""),FIND("(",REPLACE(B2,1,FIND("» ",B2),""))-1)),0)
=IFERROR(TRIM(LEFT(REPLACE(B2,1,FIND("|",SUBSTITUTE(B2,"» ","|",2)),""),FIND("(",REPLACE(B2,1,FIND("|",SUBSTITUTE(B2,"» ","|",2)),""))-1)),"")

Is there an equivalent Power Query formula I can use in the "Add Custom Column"?

Thanks!

 

[akice]

Not sure this helps, but here is a page that lists Text functions for PQ. https://msdn.microsoft.com/en-us/library/mt296604.aspx Keep in mind PQ functions are case sensitive.