+ Reply to Thread
Results 1 to 11 of 11

Converting One Column of Data into Three Columns

  1. 02-01-2014, 06:44 PM #1
    bubba1961
    bubba1961 is offline
    Registered User
    Join Date
    01-15-2014
    Location
    Harrisburg, IL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Converting One Column of Data into Three Columns

    I have an Excel spreadsheet of automotive bearing part number interchange information with one column of data that I want to divide into three columns of data. The data consists of a part#, manufacturer, and Timken part#. For example "000843506 LEE .......................... 311TB." One problem is there are inconsistent rules between each data section. For instance there appears to be one or more spaces between the part# field and the manufacturer field. There is also a lot of inconsistency between the manufacturer field and the Timkin part#. It can have a series of spaces and periods of different lengths or just a series of spaces without periods of different lengths. Can someone please help me? Thank you
    Reply With QuoteRegister To Reply
  2. 02-01-2014, 07:49 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Converting One Column of Data into Three Columns

    Hi

    The spaces are not a problem. The Trim function will get rid of any excess spaces.

    Extracting the part number is easy a simple search for the first space and then the left command will give you that.

    The hard part is finding the last space position because excel dosn't support Reverse Character Search, however clever use of the substitute function can perform the same function. And then we can use the right function.

    Having found and extracted your two part numbers, the remaining text is your manufacturer field.

    So it is achievable

    Ok I put Pseudo Text with numerous spaces in Cell B1 "000843506 LEE .... … . ....... .. .... .. ... 311TB"

    The formula in C1 removes superflous spaces. 
    Please Login or Register  to view this content.
    The formula in D1 fnds the first space and extracts the text to the left of it. 
    Please Login or Register  to view this content.
    The formula in F1 finds the last space and extracts the text to the right of it.

    Please Login or Register  to view this content.
    The last formula in E1 uses string lengths to extract the manufacturer field.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 02-01-2014 at 08:14 PM.
    Reply With QuoteRegister To Reply
  3. 02-02-2014, 09:26 AM #3
    bubba1961
    bubba1961 is offline
    Registered User
    Join Date
    01-15-2014
    Location
    Harrisburg, IL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Converting One Column of Data into Three Columns

    mehmetcik,
    Thank you very much for the help. I did everything as instructed, however the manufacturer field still has the series of periods after it. What am I doing wrong?
    Thanks,
    James
    Reply With QuoteRegister To Reply
  4. 02-02-2014, 01:04 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Converting One Column of Data into Three Columns

    They were periods, not spaces...

    Assume your data are in A1:

    First section:
    Please Login or Register  to view this content.
    Second section:
    Please Login or Register  to view this content.
    Third section:
    Please Login or Register  to view this content.

    How's that?
    Reply With QuoteRegister To Reply
  5. 02-02-2014, 03:11 PM #5
    bubba1961
    bubba1961 is offline
    Registered User
    Join Date
    01-15-2014
    Location
    Harrisburg, IL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Converting One Column of Data into Three Columns

    Glenn,
    At first I thought that fixed it. However for the data that does not have periods, it returns a #VALUE error.
    James
    Reply With QuoteRegister To Reply
  6. 02-02-2014, 03:32 PM #6
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,676

    Re: Converting One Column of Data into Three Columns

    Take a look at this
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  7. 02-02-2014, 03:51 PM #7
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,676

    Re: Converting One Column of Data into Three Columns

    Or this if there is no space between "LEE" and "."
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-02-2014, 03:53 PM #8
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting One Column of Data into Three Columns

    Hi, Please see attached file. it all done with one formula and will work with dots or without.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  9. 02-02-2014, 04:07 PM #9
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Converting One Column of Data into Three Columns

    Hi

    I didn't code to delete the periods.

    try this code:

    =TRIM(SUBSTITUTE(SUBSTITUTE(MID(C1,SEARCH(" ",B1)+1,LEN(C1)-LEN(D1)-LEN(F1)-2),".",""),"…",""))
    Last edited by mehmetcik; 02-02-2014 at 04:15 PM.
    Reply With QuoteRegister To Reply
  10. 02-02-2014, 04:11 PM #10
    bubba1961
    bubba1961 is offline
    Registered User
    Join Date
    01-15-2014
    Location
    Harrisburg, IL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Converting One Column of Data into Three Columns

    AlKey,
    That worked like a charm! Thank you very much!
    James
    Reply With QuoteRegister To Reply
  11. 02-02-2014, 04:15 PM #11
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting One Column of Data into Three Columns

    You're welcome and thank you for your feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Converting multiple columns to rows / Converting to Panel Data
    By Stuart11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2013, 05:50 AM
  2. Help required in Converting Rows to Columns and add Column Headers
    By Swaroopa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2013, 07:15 AM
  3. Converting responses from one column to data from two other columns
    By hpofql in forum Excel General
    Replies: 2
    Last Post: 05-16-2011, 10:13 AM
  4. Converting several columns containing lists of values into a single column
    By komododragon in forum Excel General
    Replies: 3
    Last Post: 02-14-2009, 02:16 PM
  5. converting one column into several columns!
    By dafeldib in forum Excel General
    Replies: 2
    Last Post: 10-10-2008, 11:32 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1