Extracting part of a text string

Question

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.

oliverscheurich · Answer

DAM_ELK&nbsp;=TEXTAFTER(TEXTBEFORE(A1,"%")," ",LEN(TEXTBEFORE(A1,"%"))-LEN(SUBSTITUTE(TEXTBEFORE(A1,"%")," ","")))&nbsp;This formula returns the intended result in my sheet.

dam_elk · Answer

This works, thank you!!

patrick2788 · Answer

DAM_ELK&nbsp;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)
)&nbsp;

Forum Discussion

Extracting part of a text string

Share

Resources