Splitting alpha numeric cells (Microsoft 97)

Topic

New Reply

I am trying to figure out how to split a column that contains alpha numeric characters. For example, I have in Cell A1, ABC1234567,
Cell A2 has AB12345678, Cell A3 has ABCDE12345 and Cell A4 has ABC12 34 567. Is there any way of turning column A into 2 separate columns, one containing all the alpha characters and another containing all the numeric characters?

Reply | Quote

Replies

Mike

Are you looking for a VBA solution, or an worksheet formula solution?

Let me know, I maybe able to get you a starting point.

Wassim

Reply | Quote

This is your big chance to redeem yourself, Wassim! Give us an Array formula like Bob’s. After 15 minutes, I’ve given up trying to create one!
P.S. No fair using VBA! –Sam

Reply | Quote

Sammy

NO one can compete with BobU on these kind of formulas. Bob is one of a kind …
But to be honest I did have his formula as a basis to start from. Maybe something that would test if IsNumber. But then how do you split the cell after you find where to split it.

This has to be two column-formula.

Wassim

Reply | Quote

You guys need to cheat! King Bob Umlas’ answer is right here (two tweaks courtesy of me). This part extracts the numbers, must be array-entered:

{=1*MID(SUBSTITUTE(A1,” “,””),MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$10),1)),0),255)}

(So who cares if I don’t understand it?)

And, assuming the source data is in Column A, and the above is in column B, extarcting the letters is easy:

=LEFT(A1,LEN(SUBSTITUTE(A1,” “,””))-LEN(B1))

Reply | Quote

With that link, one could become extremely dangerous: a whole nother world of indecipherable formulas
to you –Sam

Reply | Quote

Bob passed me that link a couple months back, and I’ve been studying it. Good stuff!

Reply | Quote

John

I just hope Mike is paying attention to all what we are offering.

BUT I guess you need to go back to the drawing board because your formula needs some work.

If the Number are followed by Text then (1*MID(A1,ROW($1:$10),1)) gets to be Number #Value.

But all in all, its impressive.

Wassim

Reply | Quote

True. If it’s a melange of text and numbers, I think we have to go VBA.

Reply | Quote

If it’s a melange, seems like you could use CONCATENATE, IF, & the iteration, but it doesn’t work for me. –Sam

Reply | Quote

The following VBA code will split the values in Column A on Sheet1 like you want into columns B and C.

Public Sub SplitA()
Dim I As Long, J As Long
    I = 0
    With Worksheets("Sheet1").Range("A1")
        While .Offset(I, 0).Value  ""
            For J = 1 To Len(.Offset(I, 0).Value)
                If IsNumeric(Mid(.Offset(I, 0).Value, J, 1)) Then
                    Exit For
                End If
            Next J
            .Offset(I, 1).Value = Left(.Offset(I, 0), J - 1)
            .Offset(I, 2).Value = Right(.Offset(I, 0), Len(.Offset(I, 0)) - J + 1)
            I = I + 1
        Wend
    End With
End Sub

Reply | Quote

Mike

Have you been paying attention to the messages in this thread, you have some very neat examples here.

You should be able to continue with one of them.

Wassim

Reply | Quote

Thank you all for all of your responses. It’s 6:10PM on 12/4. Due to other problems that came up at woork, this is the first time that I had a chance to do anything with your suggestions.

LegareColeman: I just typed your code in and I got a Run time error ’13’: Type Mismatch relating to the line containing
While.Offset(I,0).Value””. I don’t know what I did wrong or could be wrong.

Reply | Quote

You need a space between the While and the dot in front of Offset.

Reply | Quote

Thanks Legare, but I had put the space in already and still got the error message.

Reply | Quote

Can you copy your code and paste it into a message or attach a copy of the workbook to a message?

Reply | Quote

Just anther option for you in the form of a function. The following function will return the left part of a string from th estart up to the last text character. If all your entries start with Text and then switch to numbers it should work fine.

Function ExtractText(rng As Range)
Dim i As Long, strTemp As String
    strTemp = rng.Value
    For i = 1 To Len(strTemp)
        If Not IsNumeric(Mid(strTemp, i, 1)) Then
            ExtractText = Left(strTemp, i)
        End If
    Next
End Function

If A1 contains ABC123,

then in B1 =ExtractText(A1) will return ABC,

and in C1 =RIGHT(A1,(LEN(A1)-LEN(B1))) will return 123.

Just one more option for you.

Andrew C

Reply | Quote

Mike, Legare’s code works for me. When you want to use anyone’s post, select the text and copy it to Word, then select it again in Word and copy it to VBA.

Since you seem to prefer a VBA solution, here is a general-pupose function, STRIP. It strips off whatever you don’t want, letters, numbers, or punctuation. So, in your case =STRIP(A1,”Numbers”) would give “ABC”. HTH –Sam

Option Explicit

Public Function STRIP(TEXT As Variant, OP As Variant)
' TEXT is any string
' OP is what to strip: NUMBERS, LETTERS, or PUNCTUATION
'   (only the first letter is needed: N, L, or P)
Dim i As Integer
Dim letter As String
Dim s As String
    s = ""
    For i = 1 To Len(TEXT)
        letter = Mid(TEXT, i, 1)
        If IsNumeric(letter) Then
            If Left(UCase(OP), 1) = "L" Then
                s = s & letter
            End If
        Else
            If Left(UCase(OP), 1) = "N" Then
                s = s & letter
            End If
        End If
        If Left(UCase(OP), 1) = "P" Then
            If IsNumeric(letter) Or _
                (Asc(UCase(letter)) >= Asc("A") And Asc(UCase(letter)) <= Asc("Z")) Then
                s = s & letter
            End If
        End If
    Next i
    STRIP = s
End Function

Reply | Quote

Thank you to SammyB. I did the copy past suggestion and it works. Legare, thank you for the effort. Thanks to everybody else on your suggestions. I have been trying each one.

Reply | Quote

Given the structure of your examples,

{“ABC1234567”;
“AB12345678”;
“ABC12 34 567″}

which, say, A1:A3 houses,

in B1 enter: =SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{” “,0,1,2,3,4,5,6,7,8,9},””))))),””)

in C1 enter: =SUBSTITUTE(A1,B1,””) or, depending on what you’d prefer, =SUBSTITUTE(SUBSTITUTE(A1,B1,””),” “,””)+0

Select B1:C1 and copy down.

Reply | Quote