Ok, I need a field to change a number entered into a specific format.

1-23-456-78-9A-10111 (which is comes out to NINE numeric, ONE alpha, FIVE numeric)when the input is 123456789A10111, making sure that the 10th is alpha and not numeric. That way the only acceptable input would be restricted to this specific set requiring the 10th to be alpha. It is a bit of a twist on the phone number format, but it has me boggled at best at this point. Any suggestions?

Left(text;1) & "-" &

Middle(text;2;2) & "-" &

Middle(text;4;3) & "-" &

Middle(text;7;2) & "-" &

Middle(text;9;2) & "-" &

Right(text;5)

What does this the raw data represent?

Why not create separate fields for the different parts?

I hesitate responding, because sometime what the member is asking for, really doesn't solve their problem. In other words, in the past these types of request have end up being things like DMV numbers, with each set of numbers having a specific meanings.

Lee

Edited February 15, 201016 yr by Guest

...making sure that the 10th is alpha and not numeric.

So I might add a test for that 10th character being alpha and even that the remaining characters are number as:

[color:green]Case (

NOT PatternCount ( "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ; Middle ( text ; 10 ; 1 ) )

[color:brown]OR

Length ( GetAsNumber ( text ) ) ≠ [color:brown]14 ; "Invalid" ;

Left(text;1) & "-" &

Middle(text;2;2) & "-" &

Middle(text;4;3) & "-" &

Middle(text;7;2) & "-" &

Middle(text;9;2) & "-" &

Right(text;5)

[color:green])

:smile2:

Edited February 15, 201016 yr by Guest
Changed AND to OR and 9 to 14

Hey LaRetta. Xmas is over. :