0

I have a database that exports data like this:

Example of my current dataet

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

E.G.

Net Rentable Area

desired outcome

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.

Lucas Maunier
  • 5
  • 1
  • 1
    What is the format of the "Asking Rate"? I don't see an example of it – Glenn G Nov 07 '22 at 15:33
  • What (unicode)characters are you missing there in your screenshot (square with question mark)? It's not part of the desired output. Also please see and answer the above comment about missing data. – JvdV Nov 07 '22 at 15:50
  • Also it's better to post data as data, not as a picture. – P.b Nov 07 '22 at 16:23

1 Answers1

1

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:

sample excel file

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.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • 1
    Thanks David, that works flawlessly – Lucas Maunier Nov 08 '22 at 17:00
  • How do I extract the word from the text David? – Lucas Maunier Nov 08 '22 at 17:14
  • @LucasMaunier I don't follow you 100%. Maybe you can use `TEXTSPLIT` to split a string delimited by spaces (words). If you know the word to find, you can use `TEXTBEFORE` and `TEXTAFTER` or to use any other excel search function, such as `SEARCH` or `FIND`. It depends on the context. – David Leal Nov 08 '22 at 17:57