VBA text to column problem

[cmb80]

Hi There, I have the following code that I use to split data in column B.

My problem is that when the "split" has taken place - the data isn't aligned to the correct row. For instance the data that should be in B4 - ends up in B3.

The coding is part of a long looping macro. All i want it to do is split the text to columns as in the code but to the correct line

Any help please?

Please Login or Register  to view this content.

[TMS]

I suggest that you post a sample workbook with typical data and the full code.

Regards, TMS

[cmb80]

Ok i have attached a sample.

What i need to do (and it maybe best if someone provides brand new code) - is extract the scores from column B.

The problem is that there are 2 different field types. The ones near the top are formatted as 3:1 (2:0, 1:1) (I need number 3 in column C and number 1 in column D), and the lower games are formatted 00:01 (I need 0 in column B and 1 in column C).

I am struggling with VBA code for text to columns to split the data as it keeps formatting the 0 as 12 (presumbably a time feature).

If anyone could have a look at my example and tell me the best way to extract the score as above I would be greatful

[watersev]

hi cmb80, formula option if it's acceptable solution

[cmb80]

Hi there watersev - that is more than acceptable - thank you very much for that. What took you probably 5 mins has taken me over 4 hours today!

[cmb80]

Just one quick query - will the formula work for 2 digit scores (I mean 10+) or is it only to capture single digit scores?