CODEWITHSUNDEEP
excelexcel-formula
Cherubim
Cherubim

Reputation: 35

Excel: Extract text from cell where text is always #.#

I have a bunch of text in cells but many of the cells contain some text in the format of #.# (where # is actually a number from 0-9).

I'm using this formula which works okay, but sometimes there is junk in the cell that causes the formula to return the wrong information.

=MID(B7,(FIND({"."},B7,1)-1),3)

For instance, sometimes a cell contains: "abc (1st. list) testing 8.7 yay". Thus I end up with t. instead of the desired 8.7.

Any ideas?

Thank you!

Upvotes: 0

Views: 115

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

Here is a User Defined Function that will return a numeric pattern in the string if and only if it matches the pattern you describe. If the pattern you describe is not exactly representative, you'll need to provide a better example:

Option Explicit
Function reValue(S As String)
    Dim RE As Object, MC As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\b\d\.\d\b"
    If .test(S) = True Then
        Set MC = .Execute(S)
        reValue = CDbl(MC(0))
    Else
        reValue = ""
    End If
End With
End Function

enter image description here

Upvotes: 3

Related Questions