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

Try:

Let ( [

num = Filter ( text ; "0123456789" ) ;

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

] ;

Replace ( text ; pos ; 0 ; " " )

)

Sweet

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?

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

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.

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 ; " " )

)

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!

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 ; " " )

)