Split a string in two. No delimeters

cipirica

New Member
Joined
Mar 23, 2021
Messages
21
Office Version
  1. 2019
Hi. Can anyone help me? I have strings in each cell of column A like this: AlanAlda, MarkMorrison, JohnCena, etc and i want to split these strings in column B and C like this: Alan Alda, Mark Morrison, John Cena, etc.
I need a vba for this not a function or formula. Thank you for your time.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Welcome to the Board!

Here is one way:
VBA Code:
Sub SplitNames()

    Dim r As Long
    Dim lr As Long
    Dim ln As Long
    Dim c As Long
    Dim a As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lr
'       Find length of entry
        ln = Len(Cells(r, "A"))
'       Loop through entry
        If ln > 1 Then
            For c = 2 To ln
'               Get ASCII code of character
                a = Asc(Mid(Cells(r, "A"), c, 1))
'               Check to see if character is a capital letter
                If (a >= 65) And (a <= 90) Then
'                   Return parts to columns B and C
                    Cells(r, "B") = Left(Cells(r, "A"), c - 1)
                    Cells(r, "C") = Mid(Cells(r, "A"), c)
                    Exit For
                End If
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
You are welcome.
Glad I was able to help!
:)
 
Upvote 0
Ahhh. The code works perfectly but now a got into another problem. If, for example, I have "John AlanAlda" it splits the string like this: column A John and column B AlanAlda when in fact I need "John Alan" in column A and Alda in column B. Basically I need the vba code to find an exact match of a lower case and a capital case inside the string and split it between these two. Thank you again for your patience and I hope you do not mind helping me.
 
Upvote 0
Ahhh. The code works perfectly but now a got into another problem. If, for example, I have "John AlanAlda" it splits the string like this: column A John and column B AlanAlda when in fact I need "John Alan" in column A and Alda in column B. Basically I need the vba code to find an exact match of a lower case and a capital case inside the string and split it between these two. Thank you again for your patience and I hope you do not mind helping me.
I had a sneaking suspicion that your examples were oversimplified, and may not represent the full range of possibilities. This is why names are a real pain to work with - too many variations!

In your example where there are two first names, will there ALWAYS be a space between the first two names in the original data, i.e. "John Alan..."?
 
Upvote 0
If my previous statement is true, and they are regular spaces in between the names, then this amendment to the code should work:
VBA Code:
Sub SplitNames()

    Dim r As Long
    Dim lr As Long
    Dim ln As Long
    Dim c As Long
    Dim a As Long
    Dim b As String
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lr
'       Find length of entry
        ln = Len(Cells(r, "A"))
'       Loop through entry
        If ln > 1 Then
            For c = 2 To ln
'               Get ASCII code of character
                a = Asc(Mid(Cells(r, "A"), c, 1))
                'Get character in previous spot
                b = Mid(Cells(r, "A"), c - 1, 1)
'               Check to see if character is a capital letter
                If (a >= 65) And (a <= 90) And (b <> " ") Then
'                   Return parts to columns B and C
                    Cells(r, "B") = Left(Cells(r, "A"), c - 1)
                    Cells(r, "C") = Mid(Cells(r, "A"), c)
                    Exit For
                End If
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
Some of my data is something like this: John AlanAlda, John MichaelJackson, etc. I want to split in cell A John Alan, John Michael and in column B Alda, Jackson.
 
Upvote 0
Some of my data is something like this: John AlanAlda, John MichaelJackson, etc. I want to split in cell A John Alan, John Michael and in column B Alda, Jackson.
Yes, that was my assumption.
Did you try the revised code I posted?
 
Upvote 0
If my previous statement is true, and they are regular spaces in between the names, then this amendment to the code should work:
VBA Code:
Sub SplitNames()

    Dim r As Long
    Dim lr As Long
    Dim ln As Long
    Dim c As Long
    Dim a As Long
    Dim b As String
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows, starting on row 1
    For r = 1 To lr
'       Find length of entry
        ln = Len(Cells(r, "A"))
'       Loop through entry
        If ln > 1 Then
            For c = 2 To ln
'               Get ASCII code of character
                a = Asc(Mid(Cells(r, "A"), c, 1))
                'Get character in previous spot
                b = Mid(Cells(r, "A"), c - 1, 1)
'               Check to see if character is a capital letter
                If (a >= 65) And (a <= 90) And (b <> " ") Then
'                   Return parts to columns B and C
                    Cells(r, "B") = Left(Cells(r, "A"), c - 1)
                    Cells(r, "C") = Mid(Cells(r, "A"), c)
                    Exit For
                End If
            Next c
        End If
    Next r
   
    Application.ScreenUpdating = True

End Sub
This is it. Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top