+ Reply to Thread
Results 1 to 14 of 14

Delimiter-like function to extract data from a column and put in another column

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Delimiter-like function to extract data from a column and put in another column

    All,

    I have a sheet of data in which one of the columns contains case notes followed by a time tracking entry that begins with an asterisk (*) and is followed by decimal time (.3).
    So the note would look something like this: Posted email to case *.1
    What I need to do is to extract the decimal time entries and put them all in a separate column. There are thousands of notes so it needs to work on a large scale.

    Anyone have any ideas? I have tried using the delimiter function but it does not capture the data properly. I'm thinking this may require some code/VBA

    Thank you,
    Mike

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Delimiter-like function to extract data from a column and put in another column

    Please post a sample workbook with some typical data.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delimiter-like function to extract data from a column and put in another column

    Hi, and welcome to the forum.

    Maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    Sorry! I should have thought to include a sample workbook. Please see attached.

    Thank you,
    Mike
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    Richard,

    Thank you for your reply. Strangely enough, that code did work on some note entries, but not on others. I can't find a reason why. It worked perfectly on some notes, and on others that looked nearly identical it did not. I think this is very close though, I'm trying to tweak your code to see if it will work for all entries.

    Thanks,
    Mike

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delimiter-like function to extract data from a column and put in another column

    Can you give some examples of the strings that don't work?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Delimiter-like function to extract data from a column and put in another column

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    works with the examples given.


    Regards, TMS

  8. #8
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    Richard,

    Here are a handful that did not take the formula. Many others that look nearly identical did take the formula which is strange. Thanks for your help.

    Thanks,
    Mike
    Attached Files Attached Files

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Delimiter-like function to extract data from a column and put in another column

    You have a lot of new line characters before and after the time element.

    Regards, TMS

  10. #10
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    TMS,

    Oddly I am experiencing the same thing with your code as the code provided by Richard. It works on many entries, then breaks down on others. I posted an example of entries with which the formula that Richard provided would not work, and it's the same with the code that you provided. Possibly some formatting issues with the note entries?

    Thank you,
    Mike

  11. #11
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    Ah, you are correct! I didn't see that before. Certainly that is causing the problem. Do you happen to have any idea of how I can mass-remove all new line chars after the time element? Doing that by hand with each entry would be nightmare considering there are thousands.

    Thank you,
    Mike

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delimiter-like function to extract data from a column and put in another column

    Those that don't work contain a carriage return character. Note how they wrap into two rows if you click the wrap text option.

    Not yet found a way of accommodating this in a function. ... more later...

  13. #13
    Registered User
    Join Date
    06-03-2014
    Posts
    7

    Re: Delimiter-like function to extract data from a column and put in another column

    Thanks Richard

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Delimiter-like function to extract data from a column and put in another column

    Put this subroutine in a standard module and then execute it (Run/F5)

    Please Login or Register  to view this content.

    The formulae that have been provided should then work. Run it on a copy of the workbook/worksheet.


    Regards, TMS

+ 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] Extract first cell in a row, given the column header and data point in that column.
    By pguarino in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2013, 02:27 AM
  2. Replies: 2
    Last Post: 01-29-2013, 02:46 PM
  3. [SOLVED] Split One Column at Delimiter While Copying Contents of Second Column to Populate New Rows
    By medailSacrosanct in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-01-2012, 11:37 AM
  4. Replies: 1
    Last Post: 04-23-2012, 10:27 AM
  5. Replies: 3
    Last Post: 02-08-2010, 06:18 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