Format Formula - Text as Number

[Madmartigan]

I imagine this will be pretty easy but I can't get it to work.



I have fields with any possibility for data; numbers, alphas, characters, spaces, whatever.

I want to write a formula that will grab the first three characters of the string only if those three characters are numbers. If the first 3 characters are not numeric, I want to return a blank. My first attempt was:

Format(Left([Text],3),"#")

The format formula seemed to have no affect. It would return the first three characters if they were numbers or alpha characters. So then I tried:

IIf(Format(Left([Text],3),"#")<1,"",Format(Left([Text],3),"#"))

That was closer but gave me #Error for anything that wasn't numeric. Instead of #Error, I want blanks.

[June7]

What do you want if the value is like "______8797dkjhlkjf" - the underscores represent spaces.

[Madmartigan]

Good question (just when I think I covered all the scenarios ).

For that I would also want to return a blank.

[June7]

I should have asked: So if any value starts with any number of spaces, return a blank even if spaces are followed by numbers?

[Madmartigan]

Correct. I only want the first three characters if they are numeric. All other scenarios I want to return a blank.

[June7]

Crud! As far as I can tell, this requires checking each of the first 3 characters individually and determining if ALL are numerals.

Try:

IIf(IsNumeric(Left(x,1)) AND IsNumeric(Mid(x,2,1)) AND IsNumeric(Mid(x,3,1)), Val(x), Null)

[Madmartigan]

Hmmm. It seemed to work but I had one record return with:

2012

If it was working correctly I would have expected it to return:

201

[June7]

Then


IIf(IsNumeric(Left(x,1)) AND IsNumeric(Mid(x,2,1)) AND IsNumeric(Mid(x,3,1)), Left(x,3), Null)

[Madmartigan]

Perfect, thank you!