cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

How to use m code with (Left(Right(.Range to extract data from a cell?

Hi All, 

I have a named range, that I would like to pull a partial text string from. 

The Cell A4 is:

Current Weeks : 'From: 2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)`

 

I currently have:

let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([Column1],29)),

 

But I've been Googling for 2 days.  I've tried to add 

text.length
text.end

and can't figure out how to take off the the apostrophe at the end.

 

Any advise?

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @cday 

Please try this, I've tested it and it works on the string you provided.  What makes this tricky is that the last character in the string is not an apostrophe,it's a back tick `  The character under the tilde key which is at the top left of my keyboard.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    Substr = Table.AddColumn(Source, "Substring", each Text.Middle([Column1] ,  Text.PositionOf([Column1], ":", 1) + 1 , Text.PositionOf([Column1], "`")  - Text.PositionOf([Column1], ":", 1) - 1))
in
    Substr

 

 

 Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

View solution in original post

10 REPLIES 10
Super User III
Super User III

Hi @cday 

Please try this, I've tested it and it works on the string you provided.  What makes this tricky is that the last character in the string is not an apostrophe,it's a back tick `  The character under the tilde key which is at the top left of my keyboard.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    Substr = Table.AddColumn(Source, "Substring", each Text.Middle([Column1] ,  Text.PositionOf([Column1], ":", 1) + 1 , Text.PositionOf([Column1], "`")  - Text.PositionOf([Column1], ":", 1) - 1))
in
    Substr

 

 

 Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

View solution in original post

Super User III
Super User III

@cday , you may extract the substring this way,

let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    Added = Table.AddColumn(Source, "SubStr",
        each [
                positions = Text.PositionOfAny([Column1], {"'", "`"}, Occurrence.All),
                substr = Text.Range([Column1], positions{0}+1, positions{1}-positions{0}-1)
        ][substr]
    )
in
    Added

Screenshot 2020-10-27 200414.png

@cday , based on your mockup data, it's easier to change

substr = Text.Range([Column1], positions{0}+1, positions{1}-positions{0}-1)

to

substr = Text.Range([Column1], positions{0}+7, positions{1}-positions{0}-1)

to get the desired substring.

@CNENFRNL 

I copy and pasted your code, and the SubStr came almost perfect. However I don't want the "From:", Just the Date string.

Should I replace the:

positions = Text.PositionOfAny([Column1], {"'", "`"}, Occurrence.All),

With

positions = Text.PositionOfAny([Column1], {":", "`"}, Occurrence.All),

 

Also, the results in the Custom Column, just say Table..?

Capture.JPG

Super User III
Super User III

@cday ,

 

Try this code on Power Query with new column:

 

Text.Middle([Column1], 1, Text.Length([Column1]) -2 )

Capture.PNG

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 Unfortunately, The apostrophe is still there

 

Capture.JPG

@cday ,

 

Can you show me the code for this new column ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88  Sorry!

=let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "Latest 4 Wks - Ending:" & " " & Text.Middle([Column1],29, Text.Length([Column1])-2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    Custom = #"Removed Columns"{0}[Custom]
in
    Custom

@cday ,

 

You just need to adjust the 29 start number and - N number for the end.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 I tried to adjust the N number for the end, and no matter what number I use, it still does not eliminate the apostrophe...?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors