Hello
Some of my cells contain, amongst other text, times in brackets in the following format: (tt:tt) eg (23:54).
I'd like to search for these using a formula and so for example use
=SEARCH("(??:??)",A1)
and to trigger conditional formatting
=ISNUMBER(SEARCH("(??:??)",A1))
These all work but of course also detect alphabetic characters like (ab:cd)
I'd like to perform a search that only detects numbers or even better a valid time between the brackets, is this possible please?
Thanks
Robin
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
BTW are you still using XL2010? If not please amend your profile, as answers are tailored to the OP's XL version. Thanks
Hello
Yes I am still using XL2010.
This was a simple question about functions, and so creating a sample worksheet to demonstrate the problem seems to me like overkill.
In any case I have now solved the problem:
1. Assume (xx:yy) is a time.
2. Extract xx and yy and perform a TIME function: =TIME(xx,yy,0)
3. Test for an error
It's now all working as required, my conditional formatting is now highlighting cells that contain a valid time.
Cheers
Robin
Thanks for letting us know. Please mark this thread as solved.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you were able to extract the left and right portions of the cell, then it is NOT actually a time, but text that looks like a time. I think this is what was being hinted at when your workbook was requested. Real time values, like dates, are just a number, so 12:00 will change to 0.5 if you change the cell formatting to general. If the cell contents do NOT change, then this confirms that you have text and not a real time value.1. Assume (xx:yy) is a time.
2. Extract xx and yy and perform a TIME function: =TIME(xx,yy,0)
I hope this makes sense.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks