Find anything but a number or "C"

2807 views excel
8

I have this formula (below) where I am trying to find a space in C1. Instead of this, I would like to update this formula to look for anything except for "C" or any number and not only find a space.

LEFT(C1, find("" "", C1, 1)-1)

For e.g. if C1 has - "C1234 - XXX" or "C1234-XXX" or "C1234:XXX", I always want the above function to find anything except for "C" and "1234" (i.e. numbers).

P.S.: I would want to use the find function only with improvements to meet the above conditions.

Please suggest.

answered question

Is C always the first character followed by Numbers?

Will C1234 always be the first 5 characters in the searched string?

Yes. C will always be the first character, the count of numbers after C could differ. The characters after the numbers could be either symbols or english letters or space.

REGEX is your best option for this kind of thing: blog.udemy.com/vba-regex

Or maybe, can we do a find from the second character of the cell value (i.e. disregarding C) and then look for anything but a number. So if the value is "C1234-" it will return the position of - since - is not a number

1 Answer

12

I would not use formulas in vba unless absolutely necessary:

Sub getstring()

Dim t As String
t = "C1234 - XXX"

Dim i As Long
For i = Len(t) To 1 Step -1
    Dim lc As String
    If InStr("1234567890", Mid(t, i, 1)) > 0 Then
        lc = Left(t, i)
        Exit For
    End If
Next i

Debug.Print lc
End Sub

posted this

Have an answer?

JD

Please login first before posting an answer.