Need help with text calculation

[deego55]

I import many records from a text file on a regular basis. One of the fields has a book call number such as QA76.9.D3M445 1993

I need to recalculate that field so that it looks like QA 76.9 D3 M445 1993

So, in this example I need to add a space after the "A" , remove the second "." and replace with a space, and add a space after the "D3"

All of the values of this field are consistant in that they all need a space after the first two letters, replace the second "." with a space and add a space before the second letter after the removed "."

I hope this makes sense. If anyone can help me I would appreciate it greatly!! Thank you.

[-Queue-]

Let( P = Position( BookCallNo; "."; 0; 2 );

Left( BookCallNo; 2 ) & " " & Middle( BookCallNo; 3; P - 3 ) & " " & Middle( BookCallNo; P + 1; 2 ) & " " & Right( BookCallNo; Length(BookCallNo) - P - 2 ) )

You can make it an auto-entered calculation in BookCallNo and set your import options to perform auto-enter options. Deselect 'Do not replace existing value for field'. You'll probably want to disallow entry to the field in Browse Mode to prevent BookCallNo from being accidentally changed and triggering the calculation again.

[deego55]

THANK YOU!

This works perfectly!