Convert single cell text to multiple rows with information from next column

Question

I'm not sure if I'm asking this the best way, but I'll try. Basically, I have a spreadsheet for customers. Each row is one customer, and there's a cell in that row that contains keywords separated by semicolons. What I need to do now, is convert that single cell into multiple rows. So in the case of

;Cardiovascular;ENT;Eyes;General;Surgery;Neurology;Oral;Ortho;Plastics;Podiatry;Urology;

I need to convert that to 11 rows, with each row containing the same information from the parent row in all the other cells. So if that was for customer "Sprockets" then it would become

Cardiovascular Sprockets
ENT Sprockets
Eyes Sprockets
General Sprockets
Surgery Sprockets
Neurology Sprockets
Oral Sprockets
Ortho Sprockets
Plastics Sprockets
Podiatry Sprockets
Urology Sprockets

I have Excel and Access as possible tools. Any help is appreciated.

Seems l;ike something you could do in Excel with Power Query. Depending on your excel version, you could also do this with formulas fairly easily. Give one or the other a try; then edit your question with any problems you are having in encoding it. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 30, 2021 at 11:02

Ranga · Accepted Answer · 2021-03-30 11:00:47Z

1

You can use something like this

=FILTERXML("<s><t>"&SUBSTITUTE(MID(B1,2,LEN(B1)-2),";","</t><t>")&"</t></s>","/s/t") & " " & A1

answered Mar 30, 2021 at 11:00

Ranga

4302 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert single cell text to multiple rows with information from next column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related