Forum Discussion
DAM_ELK
Nov 13, 2023Copper Contributor
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.
- Patrick2788Silver Contributor
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) )
- OliverScheurichGold Contributor
=TEXTAFTER(TEXTBEFORE(A1,"%")," ",LEN(TEXTBEFORE(A1,"%"))-LEN(SUBSTITUTE(TEXTBEFORE(A1,"%")," ","")))
This formula returns the intended result in my sheet.
- DAM_ELKCopper ContributorThis works, thank you!!