find position of first alpha character

[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

 

[Gerry Kuta]

One way, may need to be adapted for your purposes.
=MID(A1,FIND(CHAR(32),A1)+1,255)
HTH

 

[Mike]

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)
____________________________________________________________________________
________________

 

[Dan E]

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

 

[Harlan Grove]

One way, may need to be adapted for your purposes.
=MID(A1,FIND(CHAR(32),A1)+1,255)

More than moderately adapted to deal with . . .
...