How to extract specific text from a sentence in Excel?-CodePudding

I have a database that exports data like this:

How can I get for instance, the Net Rentable Area with the values needed:

E.G.

Net Rentable Area

I tried the TextSplit function but I got a spill.

Please let me know what can be done, thanks!

Also it would be nice to see it working in something such as the Asking Rate, which has a different format.

CodePudding user response：

Using the TextSplit() function on this is an easy way to do it. I used =TEXTSPLIT(F9," ") and it split it perfectly. Just make sure you have empty columns to the right for the data so that you don't get the #SPILL! error

CodePudding user response：

In cell C2 you can put the following formula:

=1*TEXTSPLIT(TEXTAFTER(A2, B2&" ")," ")

Note: Multiplying by 1 ensures the result will be a number instead of a text.

and here is the output:

If all tokens to find are all words (not interpreted as numbers), then you can use the following without requiring to specify the token to find:

=LET(split, 1*TEXTSPLIT(A2," "), FILTER(split, ISNUMBER(split)))

Under this assumption you can even have the corresponding array version as follow:

=LET(rng, A2:A100, input, FILTER(rng, rng <>""), IFERROR(DROP(REDUCE(0, input,
 LAMBDA(acc,text, LET(split, 1*TEXTSPLIT(text," "), 
 nums, FILTER(split, ISNUMBER(split),""), VSTACK(acc, nums)))),1),"")
)

Note: It uses the trick for creating multiple rows using VSTACK within REDUCE. An idea suggested by @JvdV from this answer. It assumes A1 has the title of the column, if not you can use A:A instead.