+ Reply to Thread
Results 1 to 6 of 6

splitting data from 1 column to 2

  1. #1
    Registered User
    Join Date
    05-17-2021
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    16
    Posts
    3

    splitting data from 1 column to 2

    Hi all,

    I have a column with over 400 rows each cell (has # represents number) ######## three spaces and then text up to 60 characters. I want to split them number and text into 2 separate columns where the spaces are.

    Any help is greatly appreciated.

    Example 12345678 Here starts the text blah blah blah

    Thanks bunches!!!

  2. #2
    Registered User
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: splitting data from 1 column to 2

    Best way to help you is to upload an example workbook and show what the data should look like with an example of raw data.

  3. #3
    Registered User
    Join Date
    05-17-2021
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    16
    Posts
    3

    Re: splitting data from 1 column to 2

    Okay I attached a workbook with 2 sheets the raw data and what I want to see.
    Mark
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: splitting data from 1 column to 2

    You have a mixed type of data in Column B. Some of the data has only text and no numbers. How, it at all are those to be split. You have only shown an example for the cell with a combination of numbers and text.

  5. #5
    Registered User
    Join Date
    05-17-2021
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    16
    Posts
    3

    Re: splitting data from 1 column to 2

    I erred and forgot to delete the rows of just text. Please look at the cells with numbers and text

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: splitting data from 1 column to 2

    Using Power Query, I achieved your expected results.

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files

+ 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. Data Splitting In the Column
    By sathishkm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2019, 12:47 AM
  2. Splitting data based on column value
    By Ndekk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-27-2017, 04:25 PM
  3. [SOLVED] splitting data in a column
    By jaslina in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-26-2014, 04:10 PM
  4. splitting data in one column into two columns
    By steven888 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-23-2008, 02:13 AM
  5. [SOLVED] splitting one column into rows depending on the data in the column
    By ravinder.ingu@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2006, 07:50 AM
  6. Splitting Column Data into 2 fields
    By Kevin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2006, 03:10 AM
  7. [SOLVED] splitting data in a column
    By Alex in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2005, 01:05 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