VBA Text to Column, consecutive characters

[lnpasket]

Good Morning All,

Can anyone help me with using VBA to do a text to column with consecutive characters. For example, I have text in a cell which reads "PN-54826 - PCBA, 2711P, 700-1500". I want to separate the text into the following:

Cell B2: PN-54826
Cell C2: PCBA, 2711P, 700-1500

I think the way to go is say to separate at "_-_" but I am not sure how to do that without having everything with a hyphen get separated.

Please help! Thanks in advance.

 

[Joe4]

How about trying to first do a Search and replace replacing all instance of "space hypen space" with something like a pipe symbol? Then, use the pipe symbol as the delimiter from your Text-to-Columns.

 

[MickG]

Perhaps:-

Range("B2:C2") = Split(Range("A2"), " - ")

Mick

 

[lnpasket]

Thanks Mick, I think that will work for me.

Is there an easier way to apply that to a dynamic range without having to index through ever cell?

 

[Joe4]

Is there an easier way to apply that to a dynamic range without having to index through ever cell?

The solution I presented would not require you to loop through each cell.
Its just a Search and Replace on the whole column, then a Text-to-Columns on the whole column.

 

[MickG]

Try this:-

Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    Dn.Offset(, 1).Resize(, 2) = Split(Dn, " - ")
Next Dn

Mick