On Fri, 02 Apr 2010 22:47:22 -0400, Ron Rosenfeld
wrote:

On Fri, 2 Apr 2010 18:54:14 -0700 (PDT), cstang wrote:

Hi,
Can anyone pls help me with...Thanks in advance.

ColumnC
P5704
P5822(AB)
CA072407
P6384A
SGA 525
K2201/367
K402AA

Result in ColumnB as:
P
P
CA
P
SGA
K
K
Thanks in advance.

CSTANG


=LEFT(C1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C1&"01234 56789"))-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