Copy everything before number start

Hi

I have a list of label in a drop down menu like this:
MACY 147458 - ATLANTA
Hardware Electronic 149867 - GREAT
Visual Basic 0445542 - STRUCTURE

If I select “MACY 147458 – ATLANTA”, I want the cell in “B1” just spit out MACY. If I select “Hardware Electronic 149867 – GREAT”, I want cell B1 just spit out Hardware Electronic. Basically to copy everything before the number started to cell “B1”.

Thank you
VBisgreat

Assuming the control is a ActiveX control placed on the spreadsheet and not on a userform, you'd have to use something like this:

Private Sub ComboBox1_Change()

Dim str As String

str = Me.ComboBox1.Value

For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) = True Then
        Cells(1, 2).Value = Mid(str, 1, i - 1)
        Exit Sub
    End If
Next i


End Sub

Let me know if you have any questions.

Is there a formular for this? Thanks a lot. I know the =left() formular but I don't want to keep adjust the formular everytime, I want to automate this so everytime I select another lable it just spit out the name of the lable before the number begin in another cell. Thanks!

Hi VBisgreat,

Based in part from Domenis's post from here, try this (just change the cell reference from A6 to suit):

=TRIM(LEFT(A6,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A6&" 0123456789"))-1))

HTH

Robert