1. 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. 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?

3. 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. Re: Splitting cell contents (2000)

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

5. Re: Splitting cell contents (2000)

What is the rule for how you want it split?

6. 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

7. 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. 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. 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>

10. 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

11. 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. Re: Splitting cell contents (2000)

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

13. 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

14. Re: Splitting cell contents (2000)

Beginning to make sense. Thanks a lot folks.

15. 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>

Page 1 of 2 12 Last

Posting Permissions

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