# Formula to extract variable substring from string with multiple spaces

#### Royzer

##### New Member
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

Thanks!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Rick Rothstein

##### MrExcel MVP
Give this formula a try...

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

Last edited:

#### circledchicken

##### Well-known Member
Hi,

Perhaps try:
Excel Workbook
AB
1Income from transmission agreement - 83 subs @ \$0.4483
2Income from transmission agreement - 10,312 subs @ \$0.5010312
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B1=--MID(A1,38,FIND(" ",A1,38)-38)

#### wigi

##### Well-known Member
Try... with some hardcoded elements:

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

#### Royzer

##### New Member

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

##### New Member
Try... with some hardcoded elements:

Code:
``=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

##### New Member

Hi,

Perhaps try:

Sheet1
AB
1Income from transmission agreement - 83 subs @ \$0.4483
2Income from transmission agreement - 10,312 subs @ \$0.5010312

</TBODY>
Excel 2010

Worksheet Formulas
CellFormula
B1=--MID(A1,38,FIND(" ",A1,38)-38)

</TBODY>

<TBODY>
</TBODY>

circledchicken:

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

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

Thanks to you all!

#### circledchicken

##### Well-known Member
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

##### New Member
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!

Replies
5
Views
113
Replies
4
Views
259
Replies
4
Views
125
Replies
12
Views
146
Replies
0
Views
111