Split Column Data

[sewak]

For Example I have a text in A1

A1= Elevit Products - Vitamins For Pre-Conception, - Pregnancy And Breastfeeding. - www.elevit.com.au/Elevit

<tbody>
</tbody>

How to show data like

A2= Elevit Products
A3=www.elevit.com.au/Elevit
A4=Vitamins For Pre-Conception
A5=Pregnancy And Breastfeeding

For your information: Pre-Conception is one word, not separated by spaces like others.

 

[sewak]

I need to show each value separated by " - " (two space before and after dash) in A2 , A3 and A4.

 

[Gary McMaster]

Here's one way using the VBA "Split" function.

In a standard module:

Public Sub Test()

Dim oCell As Range
Dim oOutPut As Range
Dim oTarget As Range
Dim vElement As Variant
Dim vSplit As Variant

Set oTarget = ActiveSheet.Range("A1:A" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)

For Each oCell In oTarget
    Set oOutPut = oCell.Offset(0, 1)
    vSplit = Split(oCell.Text, " - ") ' Delimiter <space> <dash> <space>
    For Each vElement In vSplit
        oOutPut.Value = vElement
        Set oOutPut = oOutPut.Offset(0, 1)
    Next vElement
Next oCell

End Sub

</space></dash></space>