Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting cell contents (2000)

    I have a column containing a variety of codes, which are combinations of letters and numbers. I want to split these across several columns. So, for example, I might have AX401BL and need to split this across three columns as AX 401 and BL. There will be at most four 'blocks' alternating between text and numbers. Can someone help me create the four formulas I need? (I know this could be done easily with VBA but I need to use just Excel formulas). Thanks, Andy.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Splitting cell contents (2000)

    Hi Andy,
    Is there any pattern to this at all? By that I mean does it always start with text or could it start with numbers then switch to text?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    Do each of the cells have the same format?
    I.e. is it always 2 letters, 3 numbes 2 letters
    Or do they have arbitrary combinations? How varied are they?

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    There is no pattern, and the cell contents could start with either numbers or text.

  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 cell contents (2000)

    What is the rule for how you want it split?
    Legare Coleman

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

    Re: Splitting cell contents (2000)

    Everytime the contents switch from numeric to alpha or alpha to numeric, you need a new cell. So
    1a2b3 is split into 5 cells {1,a,2,b,3} and abc12d is split into {abc,12,d}. I feel confident that it can be done with an array formula and equally confident that only Bob Umlas can do it. I'm sending him email for help. --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>

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    That's right. Every time it alternates I need it split to a new column. However, there are at most four 'blocks' so this should keep the (array) formula to a 'reasonable' level.

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    OK, Maybe not most efficient, but:
    If text is in A1, then
    B1:=ISERROR(1*LEFT(A1,1))
    C1: array-entered: =MATCH(NOT(B1),ISERROR(1*MID(A1,ROW(1:100),1)),0)
    D1: array-entered: =MATCH(B1,ISERROR(1*MID(MID(A1,C1,255),ROW(1:100), 1)),0)
    E1: Array-entered: =MATCH(NOT(B1),ISERROR(1*MID(MID(A1,C1+D1,255),ROW (1:100),1)),0)
    Solution cell 1: =LEFT(A1,C1-1)
    Solution cell 2: =MID(A1,C1,D1-1)
    Solution cell 3: =MID(A1,C1+D1-1,E1)
    Solution cell 4: =MID(A1,C1+D1+E1-1,255)

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

    Re: Splitting cell contents (2000)

    Ok, the VBA code below will split all of the cells in the current selection into the cells to the immediate right, no matter how many it takes:

    <pre>Public Sub SplitCell()
    Dim ocell As Range
    Dim I As Integer, strCell As String, strWk As String
    For Each ocell In Selection
    I = 0
    strWk = ""
    strCell = ocell.Value
    Do While Len(strCell) > 0
    If strWk <> "" And IsNumeric(strWk) <> IsNumeric(Left(strCell, 1)) Then
    ocell.Offset(0, I + 1).Value = strWk
    I = I + 1
    strWk = ""
    Else
    strWk = strWk & Left(strCell, 1)
    strCell = Right(strCell, Len(strCell) - 1)
    End If
    Loop
    ocell.Offset(0, I + 1).Value = strWk
    Next ocell
    End Sub
    </pre>

    Legare Coleman

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

    Re: Splitting cell contents (2000)

    Thank you, Bob! Array formulas make my head hurt <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    Unfortunately, there were a couple of errors:
    All of the ROW(1:100) should be absolute, ROW($1:$100)
    You need to tack on 1A1A or A1A1 to every user entry to get rid of the #N/A

    See attached workbook for a working (I hope) example. Note--this sure makes VBA look easy! --Sam
    Attached Files Attached Files
    <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>

  11. #11
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    Looks sexy, I'll give it a try. I mentioned earlier that I need to use formulas rather than VBA.

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    Actually, can you give me an idea as to what these formulae are doing? Just the jist..

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

    Re: Splitting cell contents (2000)

    If you look at my sample worksheet then

    Column A is the user-entered mess to parse
    Column C is the mess plus 1A1 or A1A, so that we always parse at least 4 groups.
    Column D is what the first character is: True means alpha, False means number.
    Column E is the starting position of the second group.
    Column F is the length of the second group + 1
    Column G is the length of the last group

    Columns I thru L use this info in a MID to pull out the groups.

    The key to this array-mess (sorry, Bob) is the ROW($1:$100), (however, I noticed in my example they got switched to $87). So, for example in column E, the array processing grabs this and generates an array {1,2,3,...,100}, then the MID changes this to an array of your characters, ie in row 1, changes AX401BL1A1 to {A,X,4,0,1,...,1}. Finally the MATCH finds the point in the array where numbers change to alpha or vise-versa.

    Hopefully, this clarifies the array-mess. I find that it is perfectly clear as long as I don't have to fully understand it or modify it, but I have yet to generate one of these gems on my own!

    For more info, <!post=simple array formulas,132645>simple array formulas<!/post> and <!post=more array-messes,93922>more array-messes<!/post> (look at the entire thread, Bob does some explaining). There are more posts like these, search for array and Bob.

    Have fun! --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>

  14. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cell contents (2000)

    Beginning to make sense. Thanks a lot folks.

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

    Re: Splitting cell contents (2000)

    Actually, your formula in column C does not work as you intended, at least on my XL 2000. Since Right() returns a string, the IsNumber always returns false, and your formula always appends 1A1, no matter what the last character is (it does not seem to affect the final results). To work as you describe, your formula needs to be changed to:

    <pre>=IF(ISNUMBER(RIGHT(A1,1)*1),A1&"A1A",A1&"1A1" )
    </pre>

    Legare Coleman

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
  •