Split data depending on colour

[needyerhelp]

I am an Excel 2003 user. I have a long list of 6000 items in Column A containing data starting off in black and ending in dark blue. Eg: ENGLAND London. I want to split this data across columns, so that in Col B I have ENGLAND and in col C I have London.

I would also be interested if for instance there was a third colour involved:
Eg: ENGLAND London Lewisham
How can I split that across 3 columns?

Is it also possible to search for fontsizes, eg: if ENGLAND is in size 12 and London in font size 7. Can one search on fontsizes or case?

This can be a formula (if possible) or VBA.

Your help is very much appreciated.
Ray

[royUK]

No inbuilt function,you will need VBA.

[DP978]

roy is correct,

the only way would be if they had a color for a particular reason (like conditional formatting) then you could figure out some way.

In 2007 you can sort by Font Color, so if you sorted then manually copy and pasted you would also be ok.

[needyerhelp]

Can you please guide me on how to do this in VBA?

Thanks

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[needyerhelp]

Hi Roy, my brother managed to come to the rescue. I will outline here what happened so others can hopefully benefit from it. This is what I had in Column A:
Counties & Cities
BEDFORDSHIRE Dunstable
BEDFORDSHIRE Kempston
BEDFORDSHIRE Leighton Buzzard
CLWYD Colwyn Bay
CLWYD Flint
COUNTY TYRONE Cookstown

The Counties were always in black while the cities in dark blue. I wanted to split the County from the City onto Columns C & D, so the first entry, for instance would have BEDFORDSHIRE in Col C and Dunstable in Col D.

This is how it was achieved:

Please Login or Register  to view this content.

Hope this would help others.
Thanks

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here