Forum Discussion
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.lengthtext.endand can't figure out how to take off the the apostrophe at the end.
Any advise?
Hi Anonymous
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 SubstrPhil
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.
10 Replies
- PhilipTreacy
Super User
Hi Anonymous
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 SubstrPhil
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. 
camargos88Community Champion
Anonymous ,
Try this code on Power Query with new column:
Text.Middle([Column1], 1, Text.Length([Column1]) -2 )- camargos88
Anonymous ,
Can you show me the code for this new column ?
CNENFRNLAnonymous , 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 AddedI 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..?
- CNENFRNL
Anonymous , 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.