Formula to extract variable substring from string with multiple spaces

Royzer

New Member
Joined
Jun 22, 2010
Messages
45
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!
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 13, 2011
Messages
2,932
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
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Try... with some hardcoded elements:

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

Royzer

New Member
Joined
Jun 22, 2010
Messages
45

ADVERTISEMENT

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
Joined
Jun 22, 2010
Messages
45

ADVERTISEMENT

Hi,

Perhaps try:

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

<THEAD>
</THEAD><TBODY>
</TBODY>
Excel 2010

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

<THEAD>
</THEAD><TBODY>
</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
Joined
Aug 13, 2011
Messages
2,932
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
Joined
Jun 22, 2010
Messages
45
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,916
Messages
5,525,619
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top