Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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: l...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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
    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.

camargos88's avatar
camargos88
Community Champion
5 years ago

Anonymous ,

 

Try this code on Power Query with new column:

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    camargos88 Unfortunately, The apostrophe is still there

     

    • camargos88's avatar
      camargos88
      Community Champion
      5 years ago

      Anonymous ,

       

      Can you show me the code for this new column ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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