Formula to extract variable substring from string with multiple spaces

[Royzer]

Hi.

I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.

For example:

Income from transmission agreement - 83 subs @ $0.44

In this case I need to extract: 83

Income from transmission agreement - 10,312 subs @ $0.50

Need: 10,312


I would really appreciate any help you can give me.

Thanks!

 

[Rick Rothstein]

Give this formula a try...

=LOOKUP(9.9E+307,--LEFT(TRIM(MID(A2,FIND("-",A2&"-")+1,LEN(A2))),ROW(INDIRECT("1:"&LEN(A2)))))

 

[circledchicken]

Hi,

Perhaps try:

Excel Workbook
	A	B
1	Income from transmission agreement - 83 subs @ $0.44	83
2	Income from transmission agreement - 10,312 subs @ $0.50	10312
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=--MID(A1,38,FIND(" ",A1,38)-38)

 

[wigi]

Try... with some hardcoded elements:

=MID(B1,38,FIND("subs",B1,38)-39)

 

[Royzer]

Give this formula a try...

=LOOKUP(9.9E+307,--LEFT(TRIM(MID(A2,FIND("-",A2&"-")+1,LEN(A2))),ROW(INDIRECT("1:"&LEN(A2)))))

It's a cliche', but it worked like a charm! Thanks Rick.

 

[Royzer]

Try... with some hardcoded elements:

=MID(B1,38,FIND("subs",B1,38)-39)

Wigi - If I make two minor changes it works great:

=MID(B1,40,FIND("subs",B1,38)-40)

Thank you!

 

[Royzer]

Hi,

Perhaps try:

Sheet1[TABLE="class: html-maker-worksheet"]
<THEAD>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH]1[/TH]
[TD]Income from transmission agreement - 83 subs @ $0.44[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Income from transmission agreement - 10,312 subs @ $0.50[/TD]
[TD="align: right"]10312[/TD]
[/TR]
</TBODY>[/TABLE]
Excel 2010

Cell	Formula
B1	=--MID(A1,38,FIND(" ",A1,38)-38)

<TBODY>
[TD="bgcolor: #ffffff"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<THEAD>[TR]

</THEAD><TBODY>
</TBODY>

[/TD]
[/TR]
</TBODY>[/TABLE]

circledchicken:

This one also works when changed to position "40".

=MID(A1,40,FIND(" ",A1,40)-40)



Thanks to you all!

 

[circledchicken]

circledchicken:

This one also works when changed to position "40".

=MID(A1,40,FIND(" ",A1,40)-40)



Thanks to you all!

I assume there are two extra spaces somewhere in the sentence, perhaps at the start? By the way the double minus (--) at the start of the formula converts a string (which is returned by the MID function in this case) to an actual number.

Anyway, great - I'm glad you got it working!

 

[Royzer]

circledchicken: "By the way the double minus (--) at the start of the formula converts a string (which is returned by the MID function in this case) to an actual number."

I did not know that! Thanks!