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
– Tim Biegeleisen, Commented Feb 24, 2017 at 1:27
See Loop through column and check if cell contains specific chars. — user4039065
– user4039065, Commented Feb 24, 2017 at 1:43

Ron Rosenfeld · Accepted Answer · 2017-02-24 01:50:55Z

3

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

answered Feb 24, 2017 at 1:50

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related