Transpose single row containing semicolon separated values to column

Hi,

I have a network database which is in the following format:
the first column contains the source node and the second column contains all the nodes linked to that source node separated by semicolons.

For example, cell A1: 1 _ cell B1: 2; 3; 4; 9
[I'll use this notation: 1 -- 2; 3; 4; 9]

I need to convert this to:
1 -- 2
1 -- 3
1 -- 4
1 -- 9

Is there an easy way to do it?

Oh, the number of links varies.

Of course I could convert the text to columns and then manually transpose every single row, but the database is big and I'd like to find an automated way to do this.

Thank you very much!

Could you post a workbook with a small sample of data?

Sure! Here it is!

Example.xlsx

Three sheets: "What I have", "What I need" (first-best solution); "Acceptable" (second-best solution if first -best too difficult).

Thanks a lot!

Try this:

Please Login or Register  to view this content.

Thank you very much for your quick reply, Stephen.

Unfortunately, it throws me runtime error 424 "Object required".

I don't know if I'm doing something wrong...

Thanks a lot.

It worked for me on your file. Which line errors?

Line 6: "For Each r ..."

That's odd. Are you trying this on a different file from the one you posted here?

No, exactly the same one..

Actually, the real thing I need is a bit more complicated: here's the "real" thing.

Example2.xlsx

Thanks!

Lordy, that's quite different, why didn't you post that in the first place?!

Hi Wilco,
will this work on the Mac

Please Login or Register  to view this content.