How to remove specific pattern in query excel?

Question

I am using Microsoft 365. I have a query table in excel. There's one column I need to split it into 2 column by using number and dot and 1 space (1., 2., 3....) as an indicator where to split it. The result I want I want the formula to return 1 for the row that does not contains number and dot and 1 space (1., 2., 3....) in the first column and the second column is the texts after number and dot and 1 space (1., 2., 3....). For the row that contains number and dot and 1 space (1., 2., 3....), to return the number before the dot in the first column and the text after the space in the second column. Is it possible to do it directly in query? (Because there might be an update for the information in the source table of the query.)

The current data on my query in excel: enter image description here

what I want to see enter image description here

What I tried:

Text.Select([column name],{"0".."9","."}) :id not work, it removed all the numbers from the original text.
Delimited : did not work, the text that does not have 1. (number+dot+1 space) return in the first column, which in this case I need the row that does not have number and dot and 1 space (for example 1., 2., 3.,...) to return blank value or best case scenario is to return 1 directly from the query.

Michal · Accepted Answer · 2025-01-16 23:28:44Z

0

You can simply check if the text contains the delimiter and extract the number at the beginning of the line only if it is so. For example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCustom = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], ". ") then 
        [Part1 = Text.BeforeDelimiter([Column1], ". "), Part2 = Text.AfterDelimiter([Column1], ". ")] 
    else 
        [Part1 = "", Part2 = [Column1]]),
    ExpandCustom = Table.ExpandRecordColumn(AddCustom, "Custom", {"Part1", "Part2"})
in
    ExpandCustom

And I will leave it as an exercise for you to add additional check to validate if the line to be split actually starts with a number.

edited Jan 16 at 23:28

answered Jan 16 at 21:43

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jajangjaras Jan 16 at 23:02

thank you for your answer, but as you can see in the example table above that not all the rows start with number and dot. So the solution you suggest does not work for this case.

Michal Jan 16 at 23:24

I think you misread the comment in my solution. It works perfectly well for your provided example however it will not work correctly for those ones which don't start with a number but have a "dot" somewhere within the string.

Collectives™ on Stack Overflow

How to remove specific pattern in query excel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related