[SOLVED] Complex REGEXREPLACE with dots and special symbols in Google Sheets

Complex REGEXREPLACE with dots and special symbols in Google Sheets

I'm trying to extract values from the text (column A) into three columns with array formula in each column:

Name (column C)
Text with numbers, dots and special symbols (like ',&,’) written together.
First standalone number (column D)
It can have dots written together.
Second standalone number (column E)
It can have dots written together.
Sign (column F)
Just symbols + and -.

But now I can't get the expected results (column C:F). I've texted my formulas in H:K range, but they are bad. Is it even possible to solve it with array formulas and regexreplace?

I will be grateful for advice!

Example
https://docs.google.com/spreadsheets/d/1QKLM3rTFowKGF0WiUMsHI-UOvIJC85xxeQuIFhOILW0/edit?usp=sharing

Expected Results (copy from Google Sheets)

Text	Name	First Number	Second Number	Sign
Word01.Text2 12.34 9.87	Word01.Text2	12.34	9.87
+ 12.34 Text13 4	Text13	12.34	4	+
Word 12 -	Word	12		-
10 1.5 Text. Word	Text. Word	10	1.5
Word. Word1 23.3 1	Word. Word1	23.3	1
Word's1 7 +	Word's1	7		+
14 Word&Text 2	Word&Text	14	2
Text’s.13 1.24 4.5	Text’s.13	1.24	4.5
Word. 12 14 5	Word.	12	14

Thanks!

Solution

Added possible regex solution to your sheet here:

first number:

=INDEX(IFNA(--REGEXEXTRACT(A3:A11,"(?:^|\s)([0-9]*\.?[0-9]*?)(?:\s|$)")))

second number:

=INDEX(IFNA(--REGEXEXTRACT(A3:A11,I3:I11&".*?\s([0-9]*\.?[0-9]*?)(?:\s|$)")))

Name:

=INDEX(IFNA(TRIM(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A3:A11,"(?:^|\s)"&I3:I11&"(?:\s|$)"," "),"(?:^|\s)"&J3:J11&"(?:\s|$)"," "),IF(LEN(K3:K11),"\"&K3:K11,),""),"(?:^|\s)[0-9]+(?:\s|$)",""))))