How to split data in two separate columns

[sam143sta]

Hi,

Can you please tell me how to split data in two separate columns if data is in lower and upper case or in both... for eg...

<table border="0" cellpadding="0" cellspacing="0" width="450"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:9398;width:193pt" width="257"> <col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> <tbody><tr style="height:18.75pt" height="25"> <td class="xl65" style="height:18.75pt;width:48pt" height="25" width="64"> </td> <td class="xl66" style="border-left:none;width:193pt" width="257">A</td> <td style="width:97pt" width="129">
</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt;border-top:none" align="right" height="25">1</td> <td class="xl68" style="border-top:none;border-left:none">Gordon Calhoun WEST PALM</td> <td>
</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl71" style="height:18.75pt" height="25">
</td> <td class="xl72">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" colspan="2" style="height:15.0pt;mso-ignore:colspan" height="20">Output required</td> <td>
</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl65" style="height:18.75pt" height="25"> </td> <td class="xl69" style="border-left:none">A</td> <td class="xl66" style="border-left:none">B</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt;border-top:none" align="right" height="25">1</td> <td class="xl65" style="border-top:none;border-left:none">Gordon Calhoun </td> <td class="xl65" style="border-top:none;border-left:none">WEST PALM</td> </tr> </tbody></table>

 

[Sthrncali]

Search "Text To Columns" in Google.

 

[sam143sta]

I know that thing but i want to know any methord or formula for spliting the data by upper case and lower case....

 

[hiker95]

sam143sta,


I assume that all the UCase strings are grouped together on the right side of each string.


Sample raw data before the macro:

Excel Workbook
	A	B	C
1	1 Gordon Calhoun WEST PALM
2
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	1 Gordon Calhoun WEST PALM	1 Gordon Calhoun	WEST PALM
2
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitLUCase()
' hiker95, 08/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=572056
Dim c As Range, Sp, i As Long, L As String, R As String
Dim IsUCase As Boolean
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  L = "": R = ""
  Sp = Split(c, " ")
  For i = LBound(Sp) To UBound(Sp)
    If IsUCase = (Sp(i) Like "*[A-Z]*" And Not Sp(i) Like "*[a-z]*") = True Then
      L = L & " " & Sp(i)
    Else
      R = R & " " & Sp(i)
    End If
  Next i
  c.Offset(, 1) = Trim(L)
  c.Offset(, 2) = Trim(R)
Next c
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the SplitLUCase macro.