Split data in single cell

[jwp]

Hi

I am so close but am struggling can someone help complete my code.

I have the following in a single cell there are 2 posibilities the data is either split by " v " or a " - " I want the data split by these 2 posible characters and put into 2 cells.

Sample Data in 1 cell

Joe Bloggs v Fred Flinstone
Jeremy Clarkson - The Stig

I want this in 2 cells like
Joe Bloggs
Fred Flinstone

or
Jeremy Clarkson
The Stig


My code so far is working to split the first name with v or - but I can only paste the first name into a cell.

x=split(cell.offset(1,0), " v ")
Sheets("Temp").Range("A1") = x

Thank you for any help provided

 

[Rick Rothstein]

This code line...

x=split(cell.offset(1,0), " v ")

puts an array in the variable x and that array is always zero-based (because Split always creates zero-based arrays). To get to any element of an array, you simply supply the numerical index of the element you want. So, for an entry of "Joe Bloggs v Fred Flinstone", x(0) contains "Joe Bloggs" and x(1) contains "Fred Flintstone". That means you can just assign whichever one you want to the Value property of the cell you want it in. For example...

x=split(cell.offset(1,0), " v ")
Range("B1").Value = x(0)
Range("B2").Value = x(1)

 

[jwp]

Ah I understand that should be enough for me to complete my task. Will repost on my success.

Thank you!

 

[jwp]

Can someone help me with an IF or OR statement to use the right character of " v " or " - " to split the data depending on which character is present?

Am struggling.

Thanks

 

[Rick Rothstein]

Just pick one and replace it with the other, then do your split. Something like this...

X = Split([COLOR=red]Replace([/COLOR]Cell.Offset(1, 0)[COLOR=red], " - ", " v ")[/COLOR], " v ")
Range("B1").Value = X(0)
Range("B2").Value = X(1)

The part highlighted in red is what I added to my last response. Here I replace " - " with " v " before splitting using " v " as the delimiter.

 

[jwp]

Sweet that worked first time. I feel guilty now I should be paying you!

Thanks

 

[mikerickson]

Range("A1").Value = Split(Split(someString,"v")(0),"-")(0)
Range("A2").Value = Split(someString & "v","v")(1) & Split(someString & "-","-")(1)

 

[jwp]

Doh it breaks down if I only have one entry

Joe Smith vs Joe Bloggs
Sam Giggs - Tod Walters
George White

How can I account for the George White not to be split but left as a single entry?

Thanks again

 

[mikerickson]

Which method failed on George White? and how?

 

[jwp]

Am using Rick's VBA script it fails on a single entry.

Just tried your solution and it works on a single entry.

Thank you very much. This board just keeps producing.