From the right extract the second amount between two spaces

[fransb99]

My text strings look like this

2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00

CANTEEN OF EPNS CUTLERY 1 300.00 300.00 0.00

CRYSTAL DECANTER 1 225.00 225.00 0.00

SET BONE HANDLED BUTTER KNIVES 1 275.00 275.00 0.00

I would like to extract the the first number after the 0.00 at the end of the string between the two spaces; thus respectively
50.00
300.00
225.00
275.00

as amounts

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B
1
2	2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00	50.00
3	CANTEEN OF EPNS CUTLERY 1 300.00 300.00 0.00	300.00
4	CRYSTAL DECANTER 1 225.00 225.00 0.00	225.00
5	SET BONE HANDLED BUTTER KNIVES 1 275.00 275.00 0.00	275.00
Main

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),100))

 

[fransb99]

Thank you, have updated all information as required

 

[Fluff]

Thanks for that, did the formula work for you?

 

[fransb99]

Thanks for that, did the formula work for you?

The formula did extract the right value, but seem to render them as text, I tried old trick of "*1" or "-0" but get # value error

2 X BOOKSHELVES WITH LADDER 1 5,200.00 5,200.00 0.00	5,200.00	#VALUE!​
ORIENTAL STYLE TV CABINET 1 1,000.00 1,000.00 0.00	1,000.00
CANTEEN OF CUTLERY 1 750.00 750.00 0.00	750.00
2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00	50.00
2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00	50.00
CANTEEN OF EPNS CUTLERY 1 300.00 300.00 0.00	300​
CRYSTAL DECANTER 1 225.00 225.00 0.00	225​
SET BONE HANDLED BUTTER KNIVES 1 275.00 275.00 0.00	275​

 

[jtakw]

Hi,

Don't see how @Fluff formula wouldn't work after you *1 or -0, may be try this:

Book3.xlsx
	A	B
1	2 X BOOKSHELVES WITH LADDER 1 5,200.00 5,200.00 0.00	5200
2	ORIENTAL STYLE TV CABINET 1 1,000.00 1,000.00 0.00	1000
3	CANTEEN OF CUTLERY 1 750.00 750.00 0.00	750
4	2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00	50
5	2 X WOODEN CARVED ORIENTAL FIGURINES 1 50.00 50.00 0.00	50
6	CANTEEN OF EPNS CUTLERY 1 300.00 300.00 0.00	300
7	CRYSTAL DECANTER 1 225.00 225.00 0.00	225
8	SET BONE HANDLED BUTTER KNIVES 1 275.00 275.00 0.00	275
Sheet927

Cell Formulas
Range	Formula
B1:B8	B1	=LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100)+0

 

[Fluff]

What are your decimal & thousand separators?
If they are comma & space try

=SUBSTITUTE(SUBSTITUTE(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),100),","," "),".",",")+0