Startswith a number

May 25, 2012 6:03AM

Hi

I am trying to see if a text string starts with a number. eg:

Code
12000000
42000000
A3411231
65133DE1

So I am using Left([Code], 1) and using an OR to see if it is 0-1:

OR(Left([Code], 1) =1, Left([Code], 1) =2, Left([Code], 1) =3, Left([Code], 1) =4.......

is there a shortcut??

eg

BETWEEN(Left([Code], 1), 1, 9) or similar?

Thanks

Andy

steve · May 25, 2012 6:07AM

You can use < and > to compare two text values alphabetically.
E.g.
'5' < '6' yields true
and characters '0' to '9' run consecutively
so:
AND(LEFT([Code], 1) <= '9', LEFT([Code], 1) >= '0')

steve · May 25, 2012 6:08AM

Also consider
CATCH(NUMVALUE(LEFT([Code], 1)))

May 25, 2012 6:10AM

Thanks Steve

mustafa · May 25, 2012 6:50AM

You can also use Regular expressions to find if the value starts with a number using the following:

IF (SEARCHREGEX("^\\d", [Code], 1), "Y", "N")

Welcome!

Categories

Ideas Parade