+ Reply to Thread
Results 1 to 10 of 10

Text to column help!!!

  1. #1
    Registered User
    Join Date
    01-10-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Text to column help!!!

    Hello everyone, new here and of course asking for help.
    Please help me find the right answer to one of the tasks to get a new job.
    I'd appreciate any input!
    Here is the task:
    Please separate and sort the following list into excel with the following columns, sorted by category then start date. Please explain the steps (excel formulas or commands) you took to accomplish this.
    Start Date Category Ticket Subject

    RQ10361 - SEC Add 2017/01/01 Eevee
    RQ10195 - SEC Add 2017/01/04 Pen
    RQ10197 - SEC Add 2017/01/04 Pencil
    Jan 10 RQ10205 - New Strife
    RQ10209 - SEC Act 2017/01/10 DEGRANDE
    Jan 3 RQ10212 - SEC New Krystal
    RQ10223 - SEC Mod 2017/01/04 Blue
    RQ10226 - SEC Mod 2017/01/04 Fowlis
    RQ10230 - Sec Add 2017/01/04 John
    RQ10245 - SEC Add 2017/01/04 Clement
    RQ10245 - SEC Add 2017/01/04 DeVito
    RQ10250 - SEC Add 2017/01/04 Kyri
    RQ10255 - SEC Add 2017/01/04 Kane
    RQ10261 - Sec Add 2017/01/04 Bell
    Jan 4 RQ10263 - Pract Student Pyre
    RQ10290 - SEC Mod 2017/01/03 Salonga
    RQ10291 - SEC Mod 2017/01/03 Bob
    RQ10298 - SEC Mod 2017/01/02 Knight
    RQ10301 - SEC Add 2017/01/03 Lansbury
    RQ10306 - Sec MOD 2017/01/01 Mandy
    RQ10313 - SEC Add 2017/01/04 Conrad
    RQ10317 - SEC Add 2017/01/02 Wolfram
    RQ10319 - SEC Add 2017/01/02 Van Beil
    RQ10323 - SEC Add 2017/01/02 Cara
    RQ10326 - SEC Add 2017/01/02 Dole
    RQ10330 - Sec Add 2017/01/02 Johnson
    RQ10330 - Sec Del 2017/01/02 Dwayne
    RQ10335 - SEC Add 2017/01/03 Moore
    RQ10356 - SEC Add 2017/01/03 Collins
    RQ10360 - SEC Del 2017/01/01 Woong
    RQ10360 - SEC Mod 2017/01/01 Maiz
    RQ10361 - SEC Add 2017/01/01 Cookie
    RQ10361 - SEC Act 2017/01/01 Noctis
    RQ10362 - SEC Mod 2017/01/05 Maxwel
    RQ10365 - New 2017/01/03 Wang
    RQ10365 - New 2017/01/05 Back
    RQ10367 - Add 2017/01/03 Maiko
    RQ10370 - Add 2017/01/03 Bert
    RQ10370 - Del 2017/01/03 Artemis
    RQ10379 - Add 2017/01/03 Grief
    RQ10421 - SEC Del 2017/01/03 Smith
    RQ10461 - SEC Mod 2017/01/01 Mika

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Text to column help!!!

    Hi, welcome to the forum

    I am against showing anyone how to do this sort of thing, to get a job - if you cant do it on your own, and you then get the job, that could be considered cheating.

    I will however, assist you such that you end up being able to do this on your own.

    So, with that said, what have you tried, what worked, what didnt?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-10-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Text to column help!!!

    Thanks for the reply!
    I have tried the Text to Columns function but because the text is inconsistent I was not able to get good results.
    Just wanted to clarify a bit, it is a position of network engineer which I am. I'm not supposed to know Excel very well, I'm supposed to be able to deploy servers and VMs and Exchange etc.
    That was just one of the questions in the test. Thanks for your help!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Text to column help!!!

    1. Your headings dont seem to match your data?
    2. How are you getting this info?

  5. #5
    Registered User
    Join Date
    01-10-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Text to column help!!!

    Here is an example:
    Start Date Category Ticket Subject
    2017/01/04 SEC Add RQ10195 Pen

    That's why I'm asking for help, because of the strange task.

  6. #6
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Text to column help!!!

    I would suggest looking into the FIND function if your data is inconsistent, then some combination of LEFT, RIGHT, or MID to pull the actual data.

    You can find x amount of spaces or something along those lines and separate data that way. All you need to do is find some kind of consistency, for example your last column is always after the 3rd space from the end, while the first column is always before the "-" character.

    Something like that.

    I will not give you the answer either, but that is probably your best bet if text to columns does not work...

    Moreover, it appears your data is inconsistent in the way it is entered; first separate it into the two types of inconsistencies on separate tabs and determine your columns that way. Then combine your data afterwards.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Text to column help!!!

    Agree with TheN, and you may need to do this in 2 or more steps, perhaps start with T2C to get started, then use the suggestions above to break that down further

  8. #8
    Registered User
    Join Date
    01-10-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Text to column help!!!

    Thanks a lot to both of you!
    I guess I'll just edit the inconsistent lines to make them similar to the majority data and then use Text to Column option.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Text to column help!!!

    T2C wont get you all the way, but it will give you a good start

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Text to column help!!!

    Thanks for the feedback

+ 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. [SOLVED] duplicate text in empty cells along the column until new text appears in that column
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-23-2019, 07:01 AM
  2. Replies: 3
    Last Post: 04-21-2016, 02:52 AM
  3. Filter column by text, then export column to new workbook, replace searched text with new.
    By Headhunter234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2015, 07:48 AM
  4. Text&number based column to auto display new text in new column
    By TCF in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-24-2014, 01:56 PM
  5. Replies: 12
    Last Post: 01-08-2014, 11:28 AM
  6. Trying to write amacro to compare text in column A to garbage text in column B
    By Big Dawg Dad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2013, 03:31 PM
  7. Replies: 1
    Last Post: 09-01-2011, 12:05 AM

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