How to Extract Multiple Values from Text without VBA?

[Mekdam]

I have a text file that I converted to Excel;
Each line has $ value (S) as below:

Richard Deposited +$100000.
Bob Lost -$43.
Richard Lost -$444.
Bob Lost -$4356 and Richard won +$5500
Bob Lost -$4356 and Richard won +$3000
Bob Lost -$4356.
Bob Lost -$373 and Alles won +$1322.
Bob Lost -$388.
Alles Lost -$3.
Alles Lost -$94829.
Alles Lost -$63489.
Alles Lost -$84689 and Alles won +$9987.
Alles Lost -$60489.
Alles Lost -$799356.
Bob Lost -$432256.
Bob Lost -$435116.
Richard Lost -$63546.
Bob Lost -$324467.
Richard Lost -$4356.
Bob Lost -$4356 and Richard won +$4356.
Bob Lost -$4356 and Alles won +$5468.

I need to extract the numbers associated with the $ value and sign (-/+) without VBA; to process the mathematical equations for them in Excel.
The issue is that I have single or multiple $ value in each string;
How to do that?
Thanks in advance for help.

 

[kweaver]

There must be a better way (other than VBA)...but this might get you started. I assumed per your example that the positives are always at the end.

Book1
	A	B	C
1		Positives	Negatives
2	Richard Deposited +$100000.	100000
3	Bob Lost -$43.		-43
4	Richard Lost -$444.		-444
5	Bob Lost -$4356 and Richard won +$5500	5500	-4356
6	Bob Lost -$4356 and Richard won +$3000	3000	-4356
7	Bob Lost -$4356.		-4356
8	Bob Lost -$373 and Alles won +$1322.	1322	-373
9	Bob Lost -$388.		-388
10	Alles Lost -$3.		-3
11	Alles Lost -$94829.		-94829
12	Alles Lost -$63489.		-63489
13	Alles Lost -$84689 and Alles won +$9987.	9987	-84689
14	Alles Lost -$60489.		-60489
15	Alles Lost -$799356.		-799356
16	Bob Lost -$432256.		-4322
17	Bob Lost -$435116.		-4351
18	Richard Lost -$63546.		-63546
19	Bob Lost -$324467.		-3244
20	Richard Lost -$4356.		-4356
21	Bob Lost -$4356 and Richard won +$4356.	4356	-4356
22	Bob Lost -$4356 and Alles won +$5468.	5468	-4356
Sheet1

Cell Formulas
Range	Formula
B2:B22	B2	=IF(ISERROR(FIND("+$",A2)),"",1*MID(A2,2+FIND("+$",A2),100))
C2:C22	C2	=IF(ISERROR(FIND("-$",A2)),"",(-1)*MID(A2,2+FIND("-$",A2),FIND(" ",SUBSTITUTE(A2,"."," "))))

 

[Mekdam]

Thanks kweaver, this is really clever way, I never thought of it;
Much appreciated.

 

[kweaver]

Sorry, but that fails with other names ... will try to fix.

 

[DanteAmor]

Here are a couple of formulas to consider

varios 24ago2020.xlsm
	A	B	C
1		Positive	Negative
2	Richard Deposited +$100000.	100000
3	Bob Lost -$43.		43
4	Richard Lost -$444.		444
5	Bobanytext Lost -$4356 and Richard won +$5500	5500	4356
6	Bob Lost -$4356 and Richard won +$3000	3000	4356
7	Bob Lost -$4356.		4356
8	Bob Lost -$373.15 and Alles won +$1322.28	1322.28	373.15
9	Bob Lost -$388.		388
10	Alles Lost -$3.		3
11	Alles Lost -$94829.		94829
12	Alles Lost -$63489.		63489
13	Allescon Lost -$84689 and Alles won +$9987.	9987	84689
14	Alles Lost -$60489.		60489
15	Alles Lost -$799356.		799356
16	Bob Lost -$432256.		432256
17	Bob Lost -$435116.		435116
18	Richard Lost -$63546.		63546
19	Bob Lost -$324467.		324467
20	Richard Lost -$4356.		4356
21	Bob Lost -$4356 and Richard won +$4356.	4356	4356
22	Bob Lost -$4356 and Alles won +$5468.	5468	4356
Hoja6

Cell Formulas
Range	Formula
B2:B22	B2	=IFERROR(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("+$",A2)+1,"")," ",REPT(" ",250)),250)+0,"")
C2:C22	C2	=IFERROR(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("-$",A2)+1,"")," ",REPT(" ",250)),250)+0,"")

 

[kweaver]

I think this fixes the issue in C2 and filled down:

=IF(ISERROR(FIND("-$",A2)),"",(-1)*MID(A2,2+FIND("-$",A2),FIND(" ",RIGHT(SUBSTITUTE(A2,"."," "),LEN(A2)-1-FIND("-$",A2)))))

And I KNEW someone like Dante would come up with a slick solution!

 

[DanteAmor]

And I KNEW someone like Dante would come up with a slick solution!

Thanks for your comment. I see that you found the little issue in your formula.

 

[Mekdam]

Here are a couple of formulas to consider

varios 24ago2020.xlsm
	A	B	C
1		Positive	Negative
2	Richard Deposited +$100000.	100000
3	Bob Lost -$43.		43
4	Richard Lost -$444.		444
5	Bobanytext Lost -$4356 and Richard won +$5500	5500	4356
6	Bob Lost -$4356 and Richard won +$3000	3000	4356
7	Bob Lost -$4356.		4356
8	Bob Lost -$373.15 and Alles won +$1322.28	1322.28	373.15
9	Bob Lost -$388.		388
10	Alles Lost -$3.		3
11	Alles Lost -$94829.		94829
12	Alles Lost -$63489.		63489
13	Allescon Lost -$84689 and Alles won +$9987.	9987	84689
14	Alles Lost -$60489.		60489
15	Alles Lost -$799356.		799356
16	Bob Lost -$432256.		432256
17	Bob Lost -$435116.		435116
18	Richard Lost -$63546.		63546
19	Bob Lost -$324467.		324467
20	Richard Lost -$4356.		4356
21	Bob Lost -$4356 and Richard won +$4356.	4356	4356
22	Bob Lost -$4356 and Alles won +$5468.	5468	4356
Hoja6

Cell Formulas
Range	Formula
B2:B22	B2	=IFERROR(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("+$",A2)+1,"")," ",REPT(" ",250)),250)+0,"")
C2:C22	C2	=IFERROR(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND("-$",A2)+1,"")," ",REPT(" ",250)),250)+0,"")

Thanks a lot Dante

 

[Simon4s]

Select column A,
click on the Data tab
select text to columns
delimited, next
select space or use a $ sign or +/- sign
next

 

[DanteAmor]

Thanks a lot Dante

Glad we could help & thanks for the feedback