Extract part of text string

[khisla]

I have a column with a text sting that I need to extract the store number

Test String	Expected Result
232 אאוטלט מול הריםנוף הגליל IL213	IL213
232 אאוטלט מול הריםנוף הגליל IL213	IL213
227 אדידס אאוטלט ביג ירכא IL208	IL208
235 IL216 זכרון יעקב	IL216
235 IL216 זכרון יעקב	IL216
250 אאוטלט חוצות המפרץ חיפה IL225	IL225
222 אדידס אאוטלט בילו IL204	IL204

[techies]

Hi @khisla please try this custom column in power query editor

= if Text.Contains([Test String], "IL")
then Text.Middle([Test String], Text.PositionOf([Test String], "IL"), 5)
else null

[EricVieira]

Text.Middle(Text.Select([Test String], {"A".."Z", "0".."9"}), Text.PositionOfAny(Text.Select([Test String], {"A".."Z", "0".."9"}), {"IL"}), 5)


Text.Middle(Text.RegexReplace([Test String], ".*(IL\d{3}).*", "$1"), 0, 5)

[khisla]

Not sure I understand the calculation.  The store number does not sit within the same text location in each row therefore not sure the 5 is the same for each row.  What do I enter for Table [String]?

[FBergamaschi]

OK, sorry I thought it was always at the end

Table[String] is the column from where you need to extract the store

In this case:

Store Number = 

VAR pos = FIND ( "IL", Table[String] )

RETURN MID ( Table[String], pos, 5 )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[khisla]

Did not work.  

I have this Text.Start(Text.AfterDelimiter([סניף],"IL"),3) which worked however need to also include the IL portion.  Any idea?

[FBergamaschi]

What you are using is an M functon (Power Query)

This is the DAX section of the forum so I suggested a DAX formula for a calculated column, please try that and if it works marks this as a solution.

If you need a Power Query solution, pls post there so you get also a PQeury solution

Thanks

[FBergamaschi]

Create a calculate column

Store Number = RIGHT ( Table[String], 5 )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI