Split 1 Column to 2 By Number and Letter

[TheAccountant]

I have a column in my spreadsheet that begins with numbers (and sometimes a dash) and ends with a string of letters. I need to split the column at the first letter of the cell.

For example, I have these:

<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0><COLGROUP><COL style="WIDTH: 171pt; mso-width-source: userset; mso-width-alt: 8338" width=228><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204; mso-outline-parent: collapsed" width=197><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl105 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 171pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=228 height=20>OLD COLUMN</TD><TD class=xl109 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=119>NEW COLUMN 1</TD><TD class=xl110 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 148pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=197>NEW COLUMN 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>110107 IE EE HFA CONTRB MED</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">110107</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">IE EE HFA CONTRB MED</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1101-1112 INTL SOS MEMBER</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1101-1112</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">INTL SOS MEMBER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1101-1112 INTL SOS MEMBER</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1101-1112</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">INTL SOS MEMBER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>110110HMO/DENTL AUTODRW CLAIM</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">110110</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">HMO/DENTL AUTODRW CLAIM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1102-1107 LIFE INS CARB</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1102-1107</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LIFE INS CARB</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl106 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1101 NYS STD PREM</TD><TD class=xl108 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1101</TD><TD class=xl107 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">NYS STD PREM</TD></TR></TBODY></TABLE>

And I want to use just formulas to accomplish this, no macros or anything else. Please help?

 

[Scott R]

Okay it's not pretty but here goes:

Value in A1, this formula in B1:
=TRIM(LEFT(A1,MIN(IF(IFERROR(CODE(MID(A1,ROW(A1:A50),1)),99)>57,ROW(A1:A50)))-1))

This formula in C1:
=TRIM(SUBSTITUTE(A1,B1,""))

Enter B1 formula as array formula: Ctrl+Shift+Enter.

Assumes that split occurs where ASCII code is greater than that of a '9'.

 

[TheAccountant]

It worked great! Thanks. But now I discovered another problem. What if I have this kind of field:

<TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=253 border=0><COLGROUP><COL style="WIDTH: 190pt; mso-width-source: userset; mso-width-alt: 9252" width=253><TBODY><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl105 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 190pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=253 height=20>1011$100,000 LifeInsur</TD></TR></TBODY></TABLE>

How do I split the 1011 from the "$100,000 LifeInsur"?

 

[Rasm]

This is very ugly-but should work

Public Sub test()
    Dim Asingle As Single
    Dim Astr As String ' 1st part
    Dim AAstr As String ' 2nd part
    Dim Val2Split As String
    Val2Split = "1011$100,000 LifeInsur"
    Asingle = Val(Val2Split)
    Astr = Asingle
    AAstr = Right(Val2Split, Len(Val2Split) - Len(Astr))   
End Sub

 

[Haseeb Avarakkan]

Hello, try;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">110107 IE EE HFA CONTRB MED</td><td style="text-align: right;;">110107</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1101-1112 INTL SOS MEMBER</td><td style=";">1101-1112</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1101-1112 INTL SOS MEMBER</td><td style=";">1101-1112</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">110110HMO/DENTL AUTODRW CLAIM</td><td style="text-align: right;;">110110</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1102-1107 LIFE INS CARB</td><td style=";">1102-1107</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">1101 NYS STD PREM</td><td style="text-align: right;;">1101</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">1011$100,000 LifeInsur</td><td style="text-align: right;;">1011</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=REPLACE(<font color="Blue">A1,MATCH(<font color="Red">TRUE,INDEX(<font color="Green">ISNA(<font color="Purple">MATCH(<font color="Teal">INDEX(<font color="#FF00FF">CODE(<font color="Navy">MID(<font color="Blue">A1,ROW(<font color="Red">A$1:INDEX(<font color="Green">A:A,LEN(<font color="Purple">A1</font>)</font>)</font>),1</font>)</font>),</font>),{45,48,49,50,51,52,53,54,55,56,57},0</font>)</font>),</font>),0</font>),32767,""</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

 

[TheAccountant]

Haseeb, that worked beautifully! Thank you all for your help, I appreciate it. Hopefully I can return the favor on the message board down the road...