Data Cleansing - Text To Columns With Multiple Columns

Hello,

I have a large data set ('RAW Data' tab on attached spreadsheet) that I want to clean. It currently has multiple hospital locations for each Unique ID. Each hospital location has the hospital, city, state and country all listed in the same cell. There can be > 200 hospitals per Unique ID.

I would like to organize the data so that I can have the Unique ID, Location No.; Hospital; City; State and Country -- all as separate columns. I have the desired spreadsheet structure in the second worksheet tab ('CLEANED Data'), using the first Unique ID as an example.

I thought I could do this using Text to Columns, followed by a Vlookup using the Unique ID No., but it appears that you can not use Text to Columns for multiple columns at once.

Does anyone know how I should go about doing this?

Any help appreciated!

Hi Norie,

Thanks for your response. Would you mind giving some direction how I would apply the above?

Many thanks.

Hi Norrie,

Thank you. I applied that to the larger data set and it worked. That has definitely got me started.

Much appreciated.