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

Question

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!

You need a regex for this, and I don't think regular Excel has such support. — Tim Biegeleisen, Feb 24 '17 at 01:27
See [Loop through column and check if cell contains specific chars](http://stackoverflow.com/questions/42396598/loop-through-column-and-check-if-cell-contains-specific-chars/42398975#42398975). — , Feb 24 '17 at 01:43

score 3 · Answer 1 · answered Feb 24 '17 at 01:50

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

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

1 Answers1