how to check if a string contains only numeric numbers in vba

string vba excel isnumeric
TitanTheYaphet picture TitanTheYaphet · Apr 20, 2014 · Viewed 14k times · Source

I want to parse out the year info from a string like this one

$8995 Apr 18 2008 Honda Civic Hybrid $8995 (Orem) pic map cars & trucks - by owner

Since I retrieve this string online, sometimes the year element is not at the same place. The way I do it is to split the string by space using split function, then check if each node of the array contains only numeric digits.

However when i use the function IsNumeric, it also returns "$8995" node as true as well.

What is a good way to check if a string contains only numbers, no "$", no ".", not anything else?

Or in my situation, is there a better way to retrieve the year information?

Thanks.

Answer

user2426679 picture user2426679 · Oct 7, 2018

This can be accomplished as a single line of code, using the Like operator

Function StringIsDigits(ByVal s As String) As Boolean
    StringIsDigits = Len(s) And (s Like String(Len(s), "#"))
End Function

Related questions

VBA - convert to date

I have one more time a problem: I want to convert from Strings to dates in VBA The Strings look like: YYYY-DD-MM The date should be like: DD.MM.YYYY I know, normally you do this with the method cdate(), …

Read More
String manipulation with Excel - how to remove part of a string if another part is there?

I've done some Googling, and can't find anything, though maybe I'm just looking in the wrong places. I'm also not very adept at VBA, but I'm sure I can figure it out with the right pointers :) I have a string …

Read More
Insert characters into string in different locations

I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well. …

Read More