Extracting a number from an inconsistant text string

[binoos123]

Hi all,
Very good evening...
I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquerry

Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST

I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00

I have searched our forum and many excel forums, but didnt get a solution yet.
So request a solution as a formula, the text in B1 and need the price at A100. Any help would be highly appreciated
Thank you

 

[Gary's Student]

Try the following User Defined Function:

Public Function NumberPart(s As String) As Double
    ary = Split(s, " ")
    For Each a In ary
        If IsNumeric(a) Then
            NumberPart = CDbl(a)
            Exit Function
        End If
    Next a
    NumberPart = 0
End Function

User Defined Functions (UDFs) are very easy to install and use:


1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=NumberPart(A1)

To learn more about macros in general, see:

Getting Started with Macros and User Defined Functions

and

Getting Started with VBA in Office 2010

and

Writing Your Own Functions In VBA

for specifics on UDFs
Macros must be enabled for this to work!

 

[Rick Rothstein]

I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquerry

Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST

I need a formula to extract the stock price '2699.00' only from this text.

Assuming there is only one "word" before the number you want, give this formula a try...

=0+TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99))

 

[Rick Rothstein]

Try the following User Defined Function:

Public Function NumberPart(s As String) As Double
    ary = Split(s, " ")
    For Each a In ary
        If IsNumeric(a) Then
            NumberPart = CDbl(a)
            Exit Function
        End If
    Next a
    NumberPart = 0
End Function

I don't know if it is possible or not, so I am just mentioning it in case... hopefully there are no stock symbols that look like digits with and "E" or "D" in the middle (like 3E4 or 8D8).

 

[Gary's Student]

Thanks Rick ..................... if there where such symbols, I'd have to check the candidate for "digits and dot" only.

 

[clabulis]

Does the word "as" always occur after the number that you need to retrieve? IF so, use this (it's an array function so use "ctrl+shift+enter"):

=MID(A1,MAX((MID(LEFT(A1,FIND("as",A1)-2),ROW(1:1000),1)=" ")*(ROW(1:1000)))+1,FIND("as",A1)-1-(MAX((MID(LEFT(A1,FIND("as",A1)-2),ROW(1:1000),1)=" ")*(ROW(1:1000)))+1))

It allows for any amount of words to occur before the number you need.

Assumptions:
The first instance of the word "as" always occurs after the number needed
There is only 1 space between each block of text
The formula will be usd on text strings that are 1,000 characters or less

 

[clabulis]

Does the word "as" always occur after the number that you need to retrieve? IF so, use this (it's an array function so use "ctrl+shift+enter"):

=MID(A1,MAX((MID(LEFT(A1,FIND("as",A1)-2),ROW(1:1000),1)=" ")*(ROW(1:1000)))+1,FIND("as",A1)-1-(MAX((MID(LEFT(A1,FIND("as",A1)-2),ROW(1:1000),1)=" ")*(ROW(1:1000)))+1))

It allows for any amount of words to occur before the number you need.

Assumptions:
The first instance of the word "as" always occurs after the number needed
There is only 1 space between each block of text
The formula will be usd on text strings that are 1,000 characters or less

Just wanted to improve a little bit on my previous formula:

=RIGHT(LEFT(A1,FIND(" as ",A1)-1),MATCH(" ",LEFT(RIGHT(LEFT(A1,FIND(" as ",A1)-1),ROW(1:1000)),1),)-1)

Not only is it shorter, but it prevents any chance of confusion when finding an "as" occurrence, e.g. in the word "assumption"

 

[Rick Rothstein]

Just wanted to improve a little bit on my previous formula:

=RIGHT(LEFT(A1,FIND(" as ",A1)-1),MATCH(" ",LEFT(RIGHT(LEFT(A1,FIND(" as ",A1)-1),ROW(1:1000)),1),)-1)

Not only is it shorter, but it prevents any chance of confusion when finding an "as" occurrence, e.g. in the word "assumption"

If there is only one "word" before the number, then I would use what I posted in Message #3; however, if there could be more than one word, and if "as" always follows the number as you are assuming, then I would suggest this formula instead (it's shorter and has less function calls than your formula above)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" as ",A1)-1)," ",REPT(" ",999)),999))

 

[clabulis]

Nice formula Rick! I saw your post in Message #3 and was going to incorporate it just as you did in your last post, but I didn't want to steal your thunder

Also, your formula in Message #3 works great as well, but if binoos123 wants to keep the number exactly as seen in the text, then the "0+" should actually be taken out.

 

[Rick Rothstein]

Also, your formula in Message #3 works great as well, but if binoos123 wants to keep the number exactly as seen in the text, then the "0+" should actually be taken out.

I put the "0+" in there because the OP said this in his opening message (I added the bolding)...

"I have an excel stock price template, where I need the
current price to calculate the mid point if the chart."