vba help please

[shyamvinjamuri]

split a name into first and last name based on upper and lower case.
eg. JohnDoe to read as John Doe.

Please help
Thanks

 

[shg]

Is this question different than this one?

http://www.mrexcel.com/forum/showthread.php?t=533666

 

[shyamvinjamuri]

Sorry. It is the same question but I could not find my previous post so I reposted it again.
Thanks

 

[tushiroda]

I fiddled around with it some, and there is probably a shorter way to do this, but the below options work.

Excel Workbook
	A	B	C
1	JohnDoe	John	Doe
2	MikeWilford	Mike	Wilford
3	JaniceDoe	Janice	Doe
4	MichaelWilford	Michael	Wilford
5	JohnDoe	John	Doe
6	MikeWilford	Mike	Wilford
7	JaniceDoe	Janice	Doe
8	MichaelWilford	Michael	Wilford
Sheet2

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[shyamvinjamuri]

Thank you so much tushiroda

 

[Peter_SSs]

I could not find my previous post

Look at the blue bar at the top of the page and click Quick Links|Subscribed Threads.

Alternatively, again in the blue bar: Search|Advanced Search then you can search by your user name and other parameters to narrow your search if you want.

 

[Peter_SSs]

split a name into first and last name based on upper and lower case.
eg. JohnDoe to read as John Doe.

Please help
Thanks

Would you want John Doe in one cell or in two cells?

 

[hiker95]

shyamvinjamuri,


Sample data before the macro:

Excel Workbook
	A
1	JohnDoe
2	MikeWilford
3	JaniceDoe
4	MichaelWilford
5	JohnDoe
6	MikeWilford
7	JaniceDoe
8	MichaelWilford
9
Sheet1

After the macro:

Excel Workbook
	A
1	John Doe
2	Mike Wilford
3	Janice Doe
4	Michael Wilford
5	John Doe
6	Mike Wilford
7	Janice Doe
8	Michael Wilford
9
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 SplitName()
' hiker95, 03/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=533666
Dim c As Range, a As Long, H As String
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  H = Trim(c)
  For a = 2 To Len(H) Step 1
    If Asc(Mid(H, a, 1)) > 64 And Asc(Mid(H, a, 1)) < 91 Then
      c = Left(H, a - 1) & " " & Right(H, Len(H) - a + 1)
      Exit For
    End If
  Next a
Next c
Application.ScreenUpdating = True
End Sub

File SaveAs your workbook as a Macro Enabled workbook with extension xlsm.


Then run the SplitName macro.

 

[Ruddles]

Here's my effort. Paste this into the worksheet's code window and forget about it.

Option Explicit
 
Private Sub Worksheet_Calculate()
  Call InsertSpaces
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
  Call InsertSpaces
End Sub
 
Private Sub InsertSpaces()
 
  Const sNameColumn As String = "A" [COLOR=green]' column containing name[/COLOR]
 
  Dim iLast As Long
  Dim iRow As Long
  Dim iChar As Integer
  Dim iByte1 As Byte
  Dim iByte2 As Byte
  Dim sTemp As String
  
  Application.EnableEvents = False
  iLast = Cells(Rows.Count, sNameColumn).End(xlUp).Row
  For iRow = 1 To iLast
    Cells(iRow, sNameColumn) = Replace(Cells(iRow, sNameColumn), " ", "")
    sTemp = Left(Cells(iRow, sNameColumn), 1)
    For iChar = 2 To Len(Cells(iRow, sNameColumn))
      iByte1 = Asc(Mid(Cells(iRow, sNameColumn), iChar - 1, 1))
      iByte2 = Asc(Mid(Cells(iRow, sNameColumn), iChar, 1))
      If iByte1 >= Asc("a") And iByte1 <= Asc("z") And iByte2 >= Asc("A") And iByte2 <= Asc("Z") Then
        sTemp = sTemp & " " & Chr(iByte2)
      Else
        sTemp = sTemp & Chr(iByte2)
      End If
    Next iChar
    Cells(iRow, sNameColumn) = sTemp
  Next iRow
  Application.EnableEvents = True
   
End Sub

 

[Peter_SSs]

My suggestions. I've tried to include handling of multiple names, multiple initials, hyphenated names, names that already contain spaces.

Post back if you need help how to implement them.

A) If the split names are to appear in a single cell. Try this User-Defined Function.


<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> AddSpaces(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "([a-zA-Z])(?=[A-Z])"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    AddSpaces = RegEx.Replace(s, "$1 ")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>


Used in the sheet like this (copied down).

Excel Workbook
	A	B
1	JohnDoe	John Doe
2	MikeWilford	Mike Wilford
3	JaniceDoe	Janice Doe
4	MichaelWilford	Michael Wilford
5	JohnDoe	John Doe
6	ThomasMichaelSmith	Thomas Michael Smith
7	PPJones	P P Jones
8	PaulKDWalker	Paul K D Walker
9	MichaelJFox	Michael J Fox
10	JohnSmith-Jones	John Smith-Jones
11	John Smith	John Smith
Split Names 1 Cell

B) Each Name/Initial in a separate cell.

Run this macro from a standard module.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SplitNames()<br>    <SPAN style="color:#00007F">Dim</SPAN> RX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, ary <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> Range, cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> RX = CreateObject("VBScript.RegExp")<br>    <SPAN style="color:#00007F">Set</SPAN> myRng = Range("A1", Range("A" & Rows.Count).End(xlUp))<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> RX<br>        .Pattern = ".*?[a-zA-Z](?=[A-Z]| |$)"<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> myRng<br>            s = cl.Value<br>            <SPAN style="color:#00007F">If</SPAN> .Test(s) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> ary = .Execute(s)<br>                <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ary.Count<br>                    cl.Offset(, i).Value = Trim(ary(i - 1))<br>                <SPAN style="color:#00007F">Next</SPAN> i<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cl<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Results are:

Excel Workbook
	A	B	C	D
1	JohnDoe	John	Doe
2	MikeWilford	Mike	Wilford
3	JaniceDoe	Janice	Doe
4	MichaelWilford	Michael	Wilford
5	JohnDoe	John	Doe
6	MikeWilford	Mike	Wilford
7	JaniceDoe	Janice	Doe
8	MichaelWilford	Michael	Wilford
9	ThomasMichaelSmith	Thomas	Michael	Smith
10	PPJones	P	P	Jones
11	MichaelJFox	Michael	J	Fox
12	JohnSmith-Jones	John	Smith-Jones
13	John Smith	John	Smith
Split Names Multiple Cells