Typed data convert to columns

I have data on a1

Want formula on d1 to n1 to drag down

I have plugged results for d1 and d9

The file looks as follows

Your data is not consistent - the first few entries have a comma separating the first and second pairs and a comma-space separating the 2nd and 3rd pairs, but your final entry has only spaces separating all the pairs. Are these just typing errors, or is this representative of your real data?

Pete

Maybe we use a helper to strip of code and then use use & to combine

Further to FDibbins' suggestion, you could do Text_to_columns using space as the delimiter to get the first part of the codes into column B and the second (right-hand) part into column C - assuming that you only use one space in the codes, with a comma (only) to separate out each of the pairs that follow.

Then you could apply Find & Replace (Ctrl-H) to column C to change the / to comma.

Then use Text_to_Columns again, but on column C, using comma as the delimiter and column C as the first destination column. Then you can insert two new columns B and C, to get approximately what you want.

Hope this helps.

Pete

Text to column works , not very happy about it , will leave the thread open

VBA solution, assuming "," (Comma) as delimiter

Please Login or Register  to view this content.

John Topley , it cannot be better than this , thank you very much

Thank you for the feedback.