Extract the value between 2 spaces in a string

[Shind1]

Hi,

I have some text in a single cell (example below) but want to extract the "-3000" value form it. So essentially the value/text between the 1st and 2nd space.

Is there a simple method of doing this

Pay -3000 house gas

Thanks

 

[circledchicken]

Hi,

Perhaps try something like:

=TRIM(
    MID(
      SUBSTITUTE(A1, " ", REPT(" ", 99)),
      2 * 99 - 98,
      99))

There is a good explanation and generalised version of this provided by Rick Rothstein here:
Get Field from Delimited Text String

 

[jim may]

Maybe...

Excel 2010

	A	B	C	D	E
Pay -3000 house gas

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]-3000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-(FIND(" ",A1)+1))*1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=ISNUMBER(B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kevatarvind]

=mid(a1,find(" ",a1)+1,find(" ",a1,find(" ",a1)+1)-find(" ",a1))

 

[Shind1]

PERFECT...Thanks for the quick response.

All of the above work..

 

[fransb99]

Maybe...

Excel 2010

	A	B	C	D	E
1	Pay -3000 house gas	-3000			TRUE

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-(FIND(" ",A1)+1))*1 E1 =ISNUMBER(B1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I have to do something similar but from the right

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]

@fransb99
Please start a thread of your own for this question.

I would also 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’)