find position of first alpha character

  • Thread starter Thread starter Austin
  • Start date Start date
A

Austin

I would like to be able to determine the position of the first alpha
character in a cells contents. This would allow me to use the right
function to separate the numbers at the beginning of the cells and the alpha
contents.

For example:

123 ABC Company
XYZ Company
1234567 EFG Company


My desire is to strip the numbers at the beginning of the cell.

If anyone can help.....Thanks

Austin
 
One way, may need to be adapted for your purposes.
=MID(A1,FIND(CHAR(32),A1)+1,255)
HTH
 
If the numbers would always be followed by a space (ie you'll never have
123ABC, or don't care if you do) AND there would only be one set of numbers
followed by a space (ie 123 ABC, but never 123 456 ABC) then you could use

=RIGHT(A1,LEN(A1)+1-IF(ISNUMBER(VALUE(LEFT(A1,FIND("
",A1)))),SEARCH("?",A1,FIND(" ", A1)+1),SEARCH("?",A1)))

where A1 is where the cell you're interested in would be.

Otherwise, if you just want to strip out ALL leading numbers (plus any
leading & trailing spaces), you'd use something like this:
=trim(MID(A1,firstalpha(A1),LEN(A1)+1-firstalpha(A1)))

where firstalpha is a UserDefined Function defined as:

Public Function FirstAlpha(rng As Range) As Byte
Dim z As Byte
For z = 1 To Len(rng.Text)
If Not (IsNumeric(Mid(rng.Text, z, 1))) Then
FirstAlpha = z
Exit Function
End If
Next z
End Function

The difference between method 1 and method 2 (the UDF) is that if there are
multiple sets of numbers with a space, method 1 will only remove the first
set. However, method 2 will also remove any numbers that don't have a
following space, such as if you had "123 241Pizza", you'd only end up with
"Pizza".

HTH.


--

____________________________________________________________________________
________________
Please reply to newsgroup so everyone can benefit.
Email address is not valid (see sparkingwire.com)
____________________________________________________________________________
________________
 
Austin,

The following (kind of long) function will do what your asking and
doesn't require UDF's

=MID(A1,MIN(FIND({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";
"M";"N";"O";"P";"Q";"R";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},A1&
"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)),1000)

Dan E
 
Back
Top