Move multiple words in cells into one column with each word it its own cell

[LSQuinn]

Hello,

The included image is list of areas the people select they'd consider living by an online form.

They are able to select multiple areas that they're interested in living - if you notice that more than one cell has multiple areas A23 has - Llantrisant Cardiff Bridgend Porthcawl Port Talbot. There is no spaces between each area and I am unable to include one prior them being brought into excel.

I need a way move all areas into 1 column combined, but each area be in it's own cell. Each area will always start with a capital letter but some areas like Port Tablot may consist of two or more words.

Many thanks for any help
Lou

 

[mumps]

This macro assumes your data starts in row 2 in column A.

Sub SplitCells()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ii As Long, arr As Variant
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    For i = UBound(v) To LBound(v) Step -1
        If InStr(v(i, 1), Chr(10)) > 0 Then
            arr = Split(v(i, 1), Chr(10))
            For ii = UBound(arr) To LBound(arr) + 1 Step -1
                Rows(i + 2).EntireRow.Insert
                Range("A" & i + 2) = arr(ii)
            Next ii
            Range("A" & i + 1) = arr(LBound(arr))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub