+ Reply to Thread
Results 1 to 5 of 5

Data Cleansing - Text To Columns With Multiple Columns

  1. #1
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Data Cleansing - Text To Columns With Multiple Columns

    This might get you started.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing - Text To Columns With Multiple Columns

    Hi Norie,

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

    Many thanks.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Data Cleansing - Text To Columns With Multiple Columns

    1 Open the Visual Basic Editor (ALT+F11).

    2 Create a new module (Insert>Module).

    3 Copy the code I posted and paste it into the new module.

    4 Goto Run>Run Sub or hit F5.

  5. #5
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing - Text To Columns With Multiple Columns

    Hi Norrie,

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

    Much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 09-24-2017, 04:17 AM
  2. [SOLVED] Display columns in excel based on Data validation list in multiple columns
    By tejboyd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2017, 09:17 PM
  3. Group in a Pivot table but keep several text data columns as columns..
    By NunesJunior in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-22-2017, 04:45 PM
  4. [SOLVED] Moving multiple columns (8 x sets of 3) with data in separate rows into 3 columns
    By Emmax in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-03-2016, 01:23 PM
  5. [SOLVED] Place text from multiple columns into one cell based on another columns value
    By minesht in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2015, 05:44 PM
  6. Replies: 9
    Last Post: 12-15-2013, 10:05 PM
  7. Data Cleansing- How to fix errors in text
    By nav505 in forum Excel General
    Replies: 5
    Last Post: 02-10-2013, 04:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1