Separating Cells by Capital Letter.

[ryanhtx]

Hi All.

I would be very grateful if someone could help with the following:

I am looking for a formula that separates cells by capital letter:

so If I have "BlueShoes", I would end up with cells containing "Blue" and "Shoes".

I need this for varying lengths of words, so for example "ReallyBloodyNiceBlueShoes" would be split to "Really", "Bloody", "Nice" and "Shoes".

I have found the attached file online, however, I can't work out how to adjust it for words with more that two words.

Positive feedback will be given for all help.

Thanks in advance.

Ryan.

16391_Splitting data in cells with capital letters.xls

[mrice]

Try running this macro when the cells are selected.

Sub SplitByCapitals()
Dim Cell As Range
For Each Cell In Selection
    X = 2
    For N = 1 To Len(Cell)
        If Mid(Cell, N, 1) = UCase(Mid(Cell, N, 1)) Then
            X = X + 1
        End If
        Cells(Cell.Row, X) = Cells(Cell.Row, X) & Mid(Cell, N, 1)
    Next N
Next Cell
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[Pete_UK]

I've extended the formulae out to column H, so that you can have up to 7 words, but I had to save the file as .xlsx because of the level of nesting (but you have XL2007, so that's okay).

However, if you have 2 or more words with the same initial capital letter it doesn't work properly, as can be seen in the example file.

Hope this helps.

Pete

[Andrew-R]

I'd be very tempted to do this in VB, via a user-defined function.

Something like this, perhaps (not very neat, but it works):

Function SplitOnCapitals(ByVal sWordToSplit As String, lElement As Long) As String

Dim sElements() As String
Dim lCharLoop As Long
Dim bFirstMatch As Boolean
Dim sTmp As String

If sWordToSplit = LCase(sWordToSplit) Then
  SplitOnCapitals = sWordToSplit
Else
  bFirstMatch = True
  sTmp = Left(sWordToSplit, 1)
  sWordToSplit = sWordToSplit & "A"
  For lCharLoop = 2 To Len(sWordToSplit)
    If Mid(sWordToSplit, lCharLoop, 1) = UCase(Mid(sWordToSplit, lCharLoop, 1)) Then
      If bFirstMatch Then
        ReDim sElements(1 To 1)
        bFirstMatch = False
      Else
        ReDim Preserve sElements(1 To UBound(sElements) + 1)
      End If
      sElements(UBound(sElements)) = sTmp
      sTmp = Mid(sWordToSplit, lCharLoop, 1)
    Else
      sTmp = sTmp & Mid(sWordToSplit, lCharLoop, 1)
    End If
  Next lCharLoop
  
  If lElement > UBound(sElements) Then
    SplitOnCapitals = ""
  Else
    SplitOnCapitals = sElements(lElement)
  End If
  
End If

With this in a module you could use:

=SPLITONCAPITALS("ILikeIceCreamCones",3)

And it would return "Ice", the 3rd capitalized word in the string.

If your data was in cell A1 then you could go with:

=SPLITONCAPITALS($A1,COLUMN(A1))

And drag across 10 columns (or however many words you expect to have) and have it return each word in sequence.

Probably easier than trying to do it in formula.

[Richard Buttrey]

Hi,

Not a complete answer yet but I can't help thinking there's an array formula here.

=IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91,ROW(INDIRECT("1:"&LEN(A1))),"")

entered as an array formula will return the positions of every capital letter, i.e. 1,7,13,17 & 21.

Haven't got time just at the moment to play around with this but I suspect this can in some way be combined with a SUBSTITUTE formula to introduce a delimiter symbol to the original string, and then a simple Text To Columns will create columns from the string.

Feel free to play around with it. When I get a bit more time I'll take another look unless someone's come up with the answer in the meantime.