Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split Alpha Field | Excel Worksheet Functions | |||
number alpha list w/duplications | Excel Discussion (Misc queries) | |||
alpha field getting turned into an exponential number | Excel Programming | |||
Split the numbers in a alpha numeric case | Excel Discussion (Misc queries) | |||
in excel how do I show a number as an alpha eg 65 as sixty five? | Excel Worksheet Functions |