PLEASE HELP!!! Formula Question (Here's a challenge) :)

[Guest]

I'm trying to make a macro that will break data apart as shown below. The
macro will break one cell into multiple cell (not as easy as text to columns)
and keep doing so till no more data is needed to convert... I have cells with
some Alpha and numeric data and need to broken up. See below for example

Here's what I have:
Example 1 - WZ1 in B1, WZ2 C1, and so on
Here's what I want: WZ in C2 and "1" in D2, then WZ in E2, "2" in F2. I
need this to keep going until there is no data in row 1...

Example 2 - its not alway WZ1, it may be A23 in cell B1, in this case I'd
want A in B2 and 23 in C2...

Example 3 - other option BJS in cell E1, where I need BJ in E2 and S in F3...

Here are the conditions in short (data is never longer than 4 alpha or
number combined... ex. A123, AAAA, AB2, AAB)
If it's 2 letters and 1 number... ex BJ1 I need BJ in one column, 1 in
another column
If it's 1 letter and 2 or 3 numbers... ex A22 I need A in one column, 22 in
another
If it's 3 or 4 letters... ex AABB I need AA in one column, BB in another

 

[Guest]

I think this macro will do what you want:

Public Sub SplitCells()
'First cell must be selected when macro is started.
Dim NextCol As Integer, WhichType As Integer
NextCol% = ActiveCell.Column
Do While Len(ActiveCell.Value) > 0
Select Case FindType(ActiveCell.Value)
Case 1
Cells(ActiveCell.Row + 1, NextCol%).Value =
Left(ActiveCell.Value, 2)
Cells(ActiveCell.Row + 1, NextCol% + 1).Value =
Right(ActiveCell.Value, 1)
Case 2
Cells(ActiveCell.Row + 1, NextCol%).Value =
Left(ActiveCell.Value, 1)
Cells(ActiveCell.Row + 1, NextCol% + 1).Value =
Right(ActiveCell.Value, 2)
Case 3
Cells(ActiveCell.Row + 1, NextCol%).Value =
Left(ActiveCell.Value, 2)
Cells(ActiveCell.Row + 1, NextCol% + 1).Value =
Right(ActiveCell.Value, 1)
Case 4
Cells(ActiveCell.Row + 1, NextCol%).Value =
Left(ActiveCell.Value, 2)
Cells(ActiveCell.Row + 1, NextCol% + 1).Value =
Right(ActiveCell.Value, 2)
End Select
NextCol% = NextCol% + 2
If NextCol% > (Columns.Count - 1) Then
MsgBox "Ran out of columns", vbExclamation, "Error"
Exit Do
End If
ActiveCell.Offset(0, 1).Activate
Loop
End Sub

Function FindType(TxtIn As String) As Integer
'Determines which of the 4 types the data is for one cell
Dim x As Integer, TxtType As String
TxtType$ = vbNullString
For x = 1 To Len(TxtIn$)
If IsNumeric(Mid(TxtIn$, x%, 1)) Then
TxtType$ = TxtType$ & "N"
Else
TxtType$ = TxtType$ & "A"
End If
Next x%
Select Case TxtType$
Case "AAN"
FindType = 1
Case "ANN"
FindType = 2
Case "AAA"
FindType = 3
Case "AAAA"
FindType = 4
Case Else
FindType = -1
End Select
End Function

Paste the subroutine & function into the ThisWorkbook module of your
workbook. Select the first cell (B1 in your example), then run the macro by
selecting Tools >> Macro >> Macros >> SplitCells

Hope this helps,

Hutch

 

[Edward]

I'm trying to make a macro that will break data apart as shown below. The
macro will break one cell into multiple cell (not as easy as text to columns)
and keep doing so till no more data is needed to convert... I have cells with
some Alpha and numeric data and need to broken up. See below for example

Here's what I have:
Example 1 - WZ1 in B1, WZ2 C1, and so on
Here's what I want: WZ in C2 and "1" in D2, then WZ in E2, "2" in F2. I
need this to keep going until there is no data in row 1...

Example 2 - its not alway WZ1, it may be A23 in cell B1, in this case I'd
want A in B2 and 23 in C2...

Example 3 - other option BJS in cell E1, where I need BJ in E2 and S in F3...

Here are the conditions in short (data is never longer than 4 alpha or
number combined... ex. A123, AAAA, AB2, AAB)
If it's 2 letters and 1 number... ex BJ1 I need BJ in one column, 1 in
another column
If it's 1 letter and 2 or 3 numbers... ex A22 I need A in one column, 22 in
another
If it's 3 or 4 letters... ex AABB I need AA in one column, BB in another

I am not going to solve this for you but you should look into the
following functions:

MID, IF, ISNUMBER

One hint is when you take a MID of string to see if it is a number add
0 to the result as below:

=ISNUMBER(MID(B1,1,2)+0)

Also, your subject title reads to me, "I do not understand usenet
ettiquette or Excel and will waste people's time." People here will
be glad to help with pointed specific questions but you may not get
such a good response for such a broad "I have an emergency so drop
everything and do my work for me from scratch" question. Not sure if
that is accurate but that is how I read it. Good luck.

Edward