Results 1 to 6 of 6

Thread: Extract Certain Characters From A Text String

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Extract Certain Characters From A Text String

    I have a column of data with characters to both the left and right of a hyphen. I only want to read the information to the left of the hyphen. Yes, I could separate with text-to-columns but I don't want to separate the field. What VBA code would enable me to accomplish this? The field of data varies in length from record to record.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by bobkap View Post
    I have a column of data with characters to both the left and right of a hyphen. I only want to read the information to the left of the hyphen. Yes, I could separate with text-to-columns but I don't want to separate the field. What VBA code would enable me to accomplish this? The field of data varies in length from record to record.
    You do not say what you want to do with the text to the left of the hyphen, only that you want to "read" it. Do you want to put the text in the next cell over? Do you want to assign it to a variable within your code? Do you want to read in all of the text in all of the cell in the column into an array? Do you want to modify the text within its own cell so that only the text to the left of the hyphen remains? Something else?

  3. #3
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    You do not say what you want to do with the text to the left of the hyphen, only that you want to "read" it. Do you want to put the text in the next cell over? Do you want to assign it to a variable within your code? Do you want to read in all of the text in all of the cell in the column into an array? Do you want to modify the text within its own cell so that only the text to the left of the hyphen remains? Something else?
    Sorry. I was trying to be as brief as possible. Here are some samples of what my data looks like:
    Heartsaver CPR and First Aid Course April 13th, 2013 [example without hyphen]
    BLS Certification 2013 - 8:00am - 11:00am, April 12th 2013
    PALS Renewal Course -- 9:00am to 5:00pm - April 4th, 2013

    In looking this up, I just realized that some of my data is NOT separated by a hyphen so I am now in a bit of a pickle. What I need to do is use the information that is NOT a date or a time. This information, for example "BLS Certification 2013', I will use in a vlookup that has prices for this item in a named range. I will then have the looked-up price appear several columns to the right of this information shown above. (I hope this makes sense.) (The range will have "BLS Certification 2013" in one column with pricing in the column beside it.)

    Thanks for any help you can provide.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    I assume if there is no hyphen, a date will be there

    PHP Code:
    =IF(ISNUMBER(FIND("-",A2)),TRIM(LEFT(A2,FIND("-",A2)-1)),TRIM(LEFT(A2,LOOKUP(9.9999E+307,SEARCH({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},A2))-1))) 
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by bobkap View Post
    Sorry. I was trying to be as brief as possible. Here are some samples of what my data looks like:
    Heartsaver CPR and First Aid Course April 13th, 2013 [example without hyphen]
    BLS Certification 2013 - 8:00am - 11:00am, April 12th 2013
    PALS Renewal Course -- 9:00am to 5:00pm - April 4th, 2013

    In looking this up, I just realized that some of my data is NOT separated by a hyphen so I am now in a bit of a pickle.
    Some questions to help narrow things down some more (please answer each one of these as I have asked them for a specific reason)...

    1. Will the last part of your data always be a time and/or date (with no other text following them)?

    2. Could your data ever have the time only with no actual date (I'm guessing no for that, but figured I should check just to be sure)?

    3. When both the time and date are present, will the time always be shown before the date?

    4. Could your data ever look like this... "Descriptive text 8:00am - 11:00am, April 2nd 2013"... where there is a hyphen but it is not for separating the information from the time/date?

    5. Will your times always have an am/pm tag on them?

    6. Will the am/pm tag always follow the time without a space character separating them (as you show in your examples)?

    7. Will the month name always be a full month name or could it be abbreviated sometimes (such as Apr 23rd 2013)?

    8. Will the time and/or date always have a space character adjacent to them? What I am trying to find out here is whether you could have data that looked something like any of these...

    Descriptive text (8:00am - 11:00am) April 2nd 2013
    Descriptive text [8:00am - 11:00am] April 2nd 2013
    Descriptive text /8:00am - 11:00am/ April 2nd 2013
    and so on...

    9. And Admin's implied question... will your data always have a time and/or date in them (with a tip of the hat to my first question) somewhere?
    Last edited by Rick Rothstein; 05-23-2013 at 11:37 AM.

  6. #6
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    Some questions to help narrow things down some more (please answer each one of these as I have asked them for a specific reason)...

    1. Will the last part of your data always be a time and/or date (with no other text following them)?
    YES
    2. Could your data ever have the time only with no actual date (I'm guessing no for that, but figured I should check just to be sure)?
    NO
    3. When both the time and date are present, will the time always be shown before the date?
    YES
    4. Could your data ever look like this... "Descriptive text 8:00am - 11:00am, April 2nd 2013"... where there is a hyphen but it is not for separating the information from the time/date?
    YES

    5. Will your times always have an am/pm tag on them?
    YES

    6. Will the am/pm tag always follow the time without a space character separating them (as you show in your examples)?
    YES

    7. Will the month name always be a full month name or could it be abbreviated sometimes (such as Apr 23rd 2013)?
    YES it will always be full month name.
    8. Will the time and/or date always have a space character adjacent to them? What I am trying to find out here is whether you could have data that looked something like any of these...
    YES
    Descriptive text (8:00am - 11:00am) April 2nd 2013
    Descriptive text [8:00am - 11:00am] April 2nd 2013
    Descriptive text /8:00am - 11:00am/ April 2nd 2013
    and so on...

    9. And Admin's implied question... will your data always have a time and/or date in them (with a tip of the hat to my first question) somewhere?
    YES

    Thanks so much for taking the time to help me with this. I hope I'm not violating any rules of your forum, but here are some more examples of my actual data:

    BLS Skills Check -- 8:00am to 9:00am - May 23rd, 2013
    BLS Skills Check -- 8:00am to 9:00am - May 23rd, 2013
    ACLS Renewal Course -- 9:00am to 4:00pm -May 23rd, 2013
    ACLS Renewal Course -- 9:00am to 4:00pm -May 23rd, 2013
    BLS Skills Check -- 8:00am to 9:00am - May 23rd, 2013
    BLS Skills Check -- 8:00am to 9:00am - May 23rd, 2013
    BLS Certification 2013 - 12:00pm - 3:00pm, May 15th 2013

Similar Threads

  1. Get Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-05-2017, 01:24 AM
  2. Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 15
    Last Post: 12-20-2016, 09:47 AM
  3. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  4. Replies: 10
    Last Post: 12-10-2012, 11:28 PM
  5. Replies: 6
    Last Post: 09-26-2011, 07:39 AM

Tags for this Thread

Posting Permissions

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