Split Alpha/number
 

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

Split Alpha/number
 

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&"012345 6789"))-1)

--ron

Split Alpha/number
 

Use this UDF
http://www.ozgrid.com/VBA/Functions.htm

Function Get1stLetters(rCell As Range) As String
Dim lChar As Long
Dim strText As String

For lChar = 1 To Len(rCell)
If IsNumeric(Mid(rCell, lChar, 1)) Then
Get1stLetters = strText
Exit Function
Else
strText = strText & Mid(rCell, lChar, 1)
End If
Next lChar
Get1stLetters = Trim(strText)

End Function

Used in a Worksheet Cell like;
=Get1stLetters(C1)
and copied down.


"cstang" wrote in message
...
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

Split Alpha/number
 

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

Split Alpha/number
 

Here's one more way...

Sub GetInitialLetters()
Dim X As Long, LastRow As Long
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For X = StartRow To LastRow
Cells(X, "B").Value = Left(Cells(X, "C"), Evaluate( _
"MIN(FIND({0,1,2,3,4,5,6,7,8,9},C" & _
X & "&""0123456789""))") - 1)
Next
End Sub

--
Rick (MVP - Excel)



"cstang" wrote in message
...
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