Hello Rick
I personally generally prefer to use some basic VBA and/or VB string manipulation stuff in a User Defined Function, although I suppose, theoretically at least, an in built function might be more efficient sometimes, assuming the person who developed it knew what he was doing.
( A UDF I would also prefer to develop in an older Office version, using more fundamental things, as it’s a good chance then of always working also in newer office versions.
I am personally rather annoyed by Microsoft thinking that development is introducing new Functions, which then give backward compatibility problems, or coding no longer works sometimes as it did…. )
I also like that use of the Like operator. I have not used that Like much. I should get clued up on it. It looks like a useful extra tool to have when doing string manipulation and analysis.
( Often in the past I have got rather confused with other “Wild” card thing syntaxes , like in Find and Replace strings, Reg Ex and co. etc.
But a quick google just now on the Like operator reveals that its not so complicated, and should not take long to learn. ( Maybe a post on the Like operator could be a useful addition at excelfox tips for one of us to do sometime ) )
I see that the Val function is not without its "gotchas" either….
Application.IsNumber(Val("OCT1")) is perhaps showing us another Val "gotcha" , - Val when it doesn’t manage to return as a number, .. actually does return as a number - the number 0
Clearly that might be inconvenient some times !!!
In that specific example you gave, people might have thought that the Val had returned the date number. That would have been my initial guess, after seeing that MsgBox Application.IsNumber(Val("OCT1")) gave True
I see now that its doing that because Val("OCT1") is giving me the number 0 !!! - its doing that because it did not manage to return a number from the string "OCT1"
ValReturns0.JPG
Alan
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Bookmarks