Formula to Separate Fields

DJL

New Member
Joined
Feb 5, 2006
Messages
35
Hi

Is there any code or a formula that I can use to seperate text at each capital letter.

e.g. FredBloggs would be seperated into 2 cells?? Fred & Bloggs

Any help appreciated.

Thanks
Donna
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

poolhall

Active Member
Joined
Jan 9, 2009
Messages
350
Try this:

Select your data and run this sub

Code:
Sub test()
Dim c As Range, myLen As Integer

myLen = 2

For Each c In Selection
    Do Until myLen > Len(c)
        If Asc(Mid(c, myLen, 1)) < 97 Then
            c.Value = Left(c, myLen - 1) & "," & Right(c, Len(c) - myLen + 1)
            myLen = myLen + 1
        End If
    myLen = myLen + 1
    Loop
    myLen = 2
Next c

End Sub

it will place a comma before capital letters. Then you could text-to-columns your data. It is assumedyou're using the Latin alphabet in your data.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
Here is a version I had been working on. It inserts delimiters, and then does the Text-to-Columns all in one for you.
Code:
Sub MySplitPhrase()

'   Parses entries in column based on capital letters
    Application.ScreenUpdating = False
    
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myColumn As Long
    Dim iRow As Long
    Dim myEntry As String
    Dim myLen As String
    Dim myAscii As Integer
    Dim j As Long
    
'   Specify which column you would like to start on (numeric value of column)
    myColumn = 1
'   Specify which row your data starts on
    myStartRow = 2
    
'   Find last row
    myLastRow = Cells(Rows.Count, myColumn).End(xlUp).Row
        
'   Loop through rows
    For iRow = myStartRow To myLastRow
        myEntry = Cells(iRow, myColumn).Value
        myLen = Len(myEntry)
'   If entry is greater than 1
        If myLen > 1 Then
'   Loop through entry backwards
            For j = myLen To 2 Step -1
'   Look for capitals (ascii codes 65-90)
                myAscii = Asc(Mid(myEntry, j, 1))
                If myAscii >= 65 And myAscii <= 90 Then
'   If found captial, insert pipe symbol
                    myEntry = Left(myEntry, j - 1) & "|" & Mid(myEntry, j, myLen)
                End If
            Next j
        End If
'   Paste value back in cell
        Cells(iRow, myColumn).Value = myEntry
    Next iRow
    
'   Parse data using text to columns with pipe delimiter
    Columns(myColumn).TextToColumns Destination:=Cells(1, myColumn), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|"
    
    Application.ScreenUpdating = True
    
End Sub
 

DJL

New Member
Joined
Feb 5, 2006
Messages
35
Thanks for all the responses. I got what I needed.

Much appreciated.
Donna
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

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
Top