Formula to Separate Fields

[DJL]

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

 

[poolhall]

Try this:

Select your data and run this sub

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.

 

[Armando Montes]

Another one:
http://www.mrexcel.com/forum/<WBR>showthread.php?p=2095356#<WBR>post2095356
pgc01

 Sub CamelCase()
Dim rC As Range

With CreateObject("VBScript.RegExp"<WBR>)
    .Pattern = "([a-z])([A-Z])"
    .Global = True
    For Each rC In Selection
        rC.Parse "[" & .Replace(rC, "$1][$2") & "]", rC.Offset(, 2)
    Next rC
End With
End Sub

 

[Joe4]

Here is a version I had been working on. It inserts delimiters, and then does the Text-to-Columns all in one for you.

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]

Thanks for all the responses. I got what I needed.

Much appreciated.
Donna