Solved Help with excel seperating numbers from text

August 12, 2011 at 06:21:55
Specs: Windows 7
Hi, I am seeking help with removing the time on site from the following cell:

Technician on site 1730-1930; we changed the classes of service Jewelry, Catalog, and Salon; we changed the pickup group for 236,233, 231 so that they can pick up the night bell, and tested all is working

I simply need the time in the string of text nothing else. So I would like for it to return 1730-1930 in another cell. Unfortunately there is no consistency in the text for instance the text could say almost anything before and after the time. I have thousands of cells like this that I am needing to gather this information from.

Any help you can offer would be greatly appreciated.

Thanks, Melissa


See More: Help with excel seperating numbers from text

Report •


✔ Best Answer
August 12, 2011 at 07:36:19
Well, if the string will always be 4 digits hyphen 4 digits, without any spaces in between, try this:

=MID(A1,FIND("-",A1)-4,9)

It will find the position of the hyphen and subtract 4 from it to find the start of the time string. It will then extract 9 characters.

If there is a possibility of spaces in there someplace, things can get messy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 12, 2011 at 06:47:06
Is the time always the first numeric value in the string?

Is the time always followed by a semi-colon?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 12, 2011 at 06:52:56
yes the time is always the first numeric number value in the sting and most of the time there is not a semi-colon after the time. The semi-colon varies some technicans use a semi-colon some use a colon and some do not use any character after the time and simply put 1730-1930

Report •

#3
August 12, 2011 at 07:04:01
Is the time always proceeded by the word site?

Is the time always 4 digits hyphen 4 digits?

Will the time always contain a hyphen?

What I'm looking for is something that's consistent so that a User Defined Function (UDF) or macro can be written to search the string for the "time string" (which is really a text string) and extract it from the full string.

For example, if we we can determine where it starts and we know that it is always 9 characters long, we can extract it.

Any details that that describe some sort of consistency would help.

Keep in mind that Excel was never intended to work well with text, so you're not going to be able to this with a built in Excel function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 12, 2011 at 07:10:29
Unfortunatley the time is not always preceeded by the word site. The only constant is that the time is always 4 digits hyphen 4 digits and the time will always contain a hyphen. I am not familiar enough with excel to create a UDF but I did try the left & right options & it did not function well because of having too many variables.

Would it be possible to create a UDF with the constant of the 4 digits hyphen 4 digits?


Report •

#5
August 12, 2011 at 07:36:19
✔ Best Answer
Well, if the string will always be 4 digits hyphen 4 digits, without any spaces in between, try this:

=MID(A1,FIND("-",A1)-4,9)

It will find the position of the hyphen and subtract 4 from it to find the start of the time string. It will then extract 9 characters.

If there is a possibility of spaces in there someplace, things can get messy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 12, 2011 at 07:46:11
You my friend are a life saver! That worked perfectly. Thank you so much.

Report •


Ask Question