Extract Certain Characters From A Text String

[bobkap]

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.

[Rick Rothstein]

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?

[bobkap]

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.

[Rick Rothstein]

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?

[bobkap]

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

[Admin]

Hi

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

=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)))