# Find anything but a number or "C"

2807 views
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.

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

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