Forum Discussion

DAM_ELK's avatar
DAM_ELK
Copper Contributor
Nov 13, 2023

Extracting part of a text string

I'm trying to extract a discrete part of a text string -- a number before a certain delimiter, in this case, "%".

The string is all text except the number(s) that precede the percentage sign. I want to return, in a new column, just the number.

So if the string looks like this: "We shall be entitled to compensation in the amount of 4% of gross sales" ... I want to return just the number 4. 

Sometimes, the number is formatted as "4.0" , so I do not seem to be able to use SEARCH and add "-1" to go one character before the delimiter.

I came across TEXTBEFORE but it seems to return everything prior to the %, all the way to the start of the string. I need the formula to return only the number that precedes the "%" and which follows the prior SPACE.

Thanks for any/all suggestions. 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    DAM_ELK 

    A 365 solution that be can be used on a vector:

    =LET(
        KeepNumbers, LAMBDA(string,
            LET(
                explode, TOCOL(MID(string, SEQUENCE(LEN(string)), 1) * 1, 2),
                IFERROR(CONCAT(explode) * 1, "")
            )
        ),
        BYROW(array, KeepNumbers)
    )

     

  • DAM_ELK 

    =TEXTAFTER(TEXTBEFORE(A1,"%")," ",LEN(TEXTBEFORE(A1,"%"))-LEN(SUBSTITUTE(TEXTBEFORE(A1,"%")," ","")))

     

    This formula returns the intended result in my sheet.

Resources