=LEFT(C1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C1&"0123456789"))-1)
--ron
I suppose, since this is a programming group, I should also supply a VBA
solution.
The macro below will select a range from C1 to the last used cell in column C.
It will then process each cell according to your rules, and place the result in
the adjacent column on the left (e.g. B).
The column to process; the first cell used in that column; and the location of
the results can be changed by editing the code.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.
===================================
Option Explicit
Sub GetInitialAlpha()
Dim c As Range, rg As Range
Dim i As Long
Const Col As Long = 3 'set to column C
'set rg to range to process
Set rg = Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp))
Application.ScreenUpdating = False
'place initial text letters in column next to C
For Each c In rg
With c
.Offset(0, -1) = .Text
For i = 1 To Len(.Text)
If IsNumeric(Mid(.Text, i, 1)) Then
.Offset(0, -1).Value = Left(.Text, i - 1)
Exit For
End If
Next i
End With
Next c
Application.ScreenUpdating = True
End Sub
====================================
--ron
