Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting alpha numeric cells (Microsoft 97)

    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?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    This is your big chance to redeem yourself, Wassim! Give us an Array formula like <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=93922&page=&v iew=&sb=&o=&vc=1#Post93922>Bob's</A>. After 15 minutes, I've given up trying to create one!
    P.S. No fair using VBA! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    Sammy

    NO one can compete with BobU on these kind of formulas. Bob is one of a kind <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>...
    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

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

    <pre>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
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

    You guys need to cheat! King Bob Umlas' answer is right <A target="_blank" HREF=http://www.emailoffice.com/excel/arrays-bobumlas.html>here</A> (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?) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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))
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> With that link, one could become extremely dangerous: a whole nother world of indecipherable formulas <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> to you <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    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. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

    True. If it's a melange of text and numbers, I think we have to go VBA.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

    Bob passed me that link a couple months back, and I've been studying it. Good stuff!
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting alpha numeric cells (Microsoft 97)

    If it's a melange, seems like you could use CONCATENATE, IF, & the iteration, but it doesn't work for me. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    New Lounger
    Join Date
    Dec 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

    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.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

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

  15. #15
    New Lounger
    Join Date
    Dec 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting alpha numeric cells (Microsoft 97)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •