Search code examples
excelregexexcel-formulapowerqueryregexp-replace

Replace by regular expresion in Excel

I have a list in Excel like the following:

1 / 6 / 45
123
1546
123 456 
1247 /% 456 /

I want to create a new column with all sequences of consecutive non digits replaced by a character. In Google Sheets, this is easy using =REGEXREPLACE(A1&"/","\D+",","), resulting in:

1,6,45,
123,
1546,
123,456 
1247,456,

In that formula A1&"/" is needed in order for REGEXREPLACE to work with numbers. No big deal, just adds a comma at the end.

How can we do this in Excel? Pure Power Query (not R, not Python, just M) is very much encouraged. VBA and other clickable Excel features are unacceptable (like find and replace).

Solution

  • If you have Excel 365:

    enter image description here

    In B1:

    =LET(X,MID(A1,SEQUENCE(LEN(A1)),1),SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--X),X," ")))," ",","))

    Or if streaks of digits are always delimited by at least a space:

    =TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[.*0=0]"))

    Another option, if you have got access to it, is LAMBDA(). Make a function to replace all kind of characters, something along the lines of this. Without LAMBDA() and TEXTJOIN() I think your best bet would be to start nesting SUBSTITUTE() functions.