Distinguishing between numbers and letters

[deego55]

Hi-

I have a field that will contain a string of characters such as TK58, Z35, QA765, etc. I need to distinguish where there is a letter next to a number and add a space so that it becomes TK 58, Z 35, QA 765, etc.

Note that there isn't always the same number of letters so I dont think a Left( type funtion will work.

Is there a way to distinguish where the letters end and numbers begin?

Thanks in advance

[comment]

Try:

Let ( [

num = Filter ( text ; "0123456789" ) ;

pos = Position ( text ; num ; 1 ; 1 )

] ;

Replace ( text ; pos ; 0 ; " " )

)

[MoonShadow]

Sweet

[deego55]

thanks comment. i tried this but it does not seem to make any difference. i replaced "text" with the field name. is there anything else i need to do?

[comment]

No, that should be it. How do you want to implement the formula - a separate calc field, auto-enter, script or what?

[deego55]

a seperate calc field would be ideal. the fields contain Library of Congress call numbers. Here are a couple of examples:

QA76.73 C154F69 2002

TK5102.9 Q82 2004

Q76.76 D47M392 2003

There will never be more than two numbers at the beginning.

[comment]

Yes, I see the problem now. This will happen when the example given doesn't match the actual data. Since there are more non-numeric characters following the first numeric character, you need to change the calc to:

Let ( [

num = Filter ( yourfield ; "0123456789" ) ;

pos = Position ( yourfield ; Left ( num ; 1 ) ; 1 ; 1 )

] ;

Replace ( yourfield ; pos ; 0 ; " " )

)

This variation will do the same:

Let ( [

num = GetAsNumber ( yourfield ) ;

pos = Position ( yourfield ; Left ( num ; 1 ) ; 1 ; 1 )

] ;

Replace ( yourfield ; pos ; 0 ; " " )

)

[deego55]

that works great! you are a genius!!

one more question. is it possible to do the same type of thing for the third "word" in the string such as "A65R597" in QA 76.76 A65R597 2000. I would need to add a space before the second letter (is there is one) so that it would be "QA 76.76 A65 R597 2000

that would solve all of my problems! thank you so much!

[comment]

This is a bit more complex. I am not sure this is the simplest solution, and I hope there isn't some unforeseen combination that will trip it, but try:

Let ( [

word2 = MiddleWords ( text ; 2 ; 1 ) ;

numsToSpaces = Substitute ( word2 ; [ "0" ; " " ] ; [ "1" ; " " ] ; [ "2" ; " " ] ; [ "3" ; " " ] ; [ "4" ; " " ] ; [ "5" ; " " ] ; [ "6" ; " " ] ; [ "7" ; " " ] ; [ "8" ; " " ] ; [ "9" ; " " ] ) ;

posInWord = Position ( numsToSpaces ; MiddleWords ( numsToSpaces ; 2 ; 1 ) ; 1 ; 1 ) ;

pos1 = Length ( LeftWords ( text ; 1 ) ) + 1 + posInWord ;

newText = Case ( posInWord ; Replace ( text ; pos1 ; 0 ; " " ) ; text ) ;

num = GetAsNumber ( newText ) ;

pos = Position ( newText ; Left ( num ; 1 ) ; 1 ; 1 )

] ;

Replace ( newText ; pos ; 0 ; " " )

)