Split cell into alpha and numerics

[Nanette]

Hi fellow excelians,

I am trying to split a single cell into two cells of alpha & numerics. The cell values differ in length from 4 characters to 9 characters. The number of alpha chars differs between cells as does the number of numerics, the only thing consistant is that the alpha characters are on the left and the numerics are on the right. Shown is a typical subset of the 12,600 rows.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>AU73216</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>X82665</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AI01436</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ILOO088</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AA2247</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>X82620</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>X82661</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>GB05684</TD></TR></TBODY></TABLE>

Any help would be invaluable
Kind regards
Nanette

 

[stanleydgromjr]

Nanette,

See:
Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters
http://www.exceltip.com/st/Subdivid...ontaining_only_Numbers_or_Characters/897.html


Have a great day,
Stan

 

[stanleydgromjr]

Nanette,

Excel Workbook
	A	B	C
1	AU73216	AU	73216
2	X82665	X	82665
3	AI01436	AI	01436
4	ILOO088	ILOO	088
5	AA2247	AA	2247
6	X82620	X	82620
7	X82661	X	82661
8	GB05684	GB	05684
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module.

Option Explicit

Function GetChars(target As Range)
' thomach
' http://www.ozgrid.com/forum/showthread.php?t=63632
'
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetChars = MyStr
End Function
 
Function GetNums(target As Range)
' thomach
' http://www.ozgrid.com/forum/showthread.php?t=63632
'
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetNums = MyStr
End Function

Have a great day,
Stan

 

[jindon]

UDF
=TextNum(A1,1)
1 for Text only, 0 for Numbers only

Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(ref = True, "\d+", "\D+")
    .Global = True
    TextNum = .replace(txt, "")
End With
End Function

 

[Nanette]

Thank you very very much. You are both brilliant.

Stan I have been able to insert your code and run it with no hassels.
I appreciate your help immensely, and feel all the the more clever for it.

Thanks heaps

Nanette

 

[bosco_yip]

1] Assume data put in : A2:A9

2] To extract numeric : B2, enter array formula and copy down :

{=MID(A2,MIN(FIND(ROW($1:$10)-1,A2&5^19)),255)}

3] To extract alpha : C2, enter formula and copy down :

=SUBSTITUTE(A2,B2,"")

Regards
Bosco