I have a text field with phone numbers like:

301.555.1212 vp

301.555.3232 vb

I want to separate the data into two fields (voice personal and voice business), as it should have been done in the first place.

I've created a second field for the business phone number, then set that field so I have the same data as what is now the personal voice field.

I'm looking to build a script that will set the personal voice field so that the business phone and its suffix (" vb"), as well as the " vp" suffix are removed, leaving only the 'voice personal' number.

Something along the lines of:

show all records, go to the first record, loop, , repeat to next, exit after last.

Here's what I've got so far, for the "voiceP" field, but it isn't correct yet.

--

Let ( [

endKeeper = Position ( contacts::voiceP ; " vp"; 1 ; 1);

startKeeper = endKeeper - 12 ] ;

If (

PatternCount ( contacts::voiceP ; " vp" ) > 0 ;

Middle ( contacts::voiceP; startKeeper; endKeeper - startKeeper);

contacts::voiceP )

)

--

Thanks.

Bob

Edited December 9, 200718 yr by Guest
This works better. Now I'll try Lionel's code.

bob

See if this what you want

goto first record

Loop

Set field (your field; leftwords(phone,1)

goto next records exit after last

end loop

Lionel

Lionel,

Your code works great if the first line is the personal voice line. Unfortunately, some records have that on the second line (I didn't mention that, sorry).

I redid my code (see my original post), and it is working so far, however I like that fact that yours works on different length phone numbers, so I'll try to wrap that part into some other code that works regardless of the position of the personal and business phone number. This is a great help!

Bob

Try something like:

Let ( [

t = ¶ & YourField ;

end = Position ( t ; "vp"; 1 ; 1 ) ;

start = Position ( t ; ¶ ; end ; -1 ) + 1

] ;

Trim ( Middle ( t ; start ; end - start ) )

)

Note that instead of looping you could use Replace Field Contents[] or, even better, change the target field into a calculation field. Once you're satisfied with the results, change it back to a Text field.

comment,

Thanks, that works perfectly when my data is exactly how I originally described it. I just found that when my data is otherwise, such as " vp" instead of " vh", or the first line ending with " vb" and the second with " vh", there end up being problems. However I can see how using your logic with some additional code might be really robust. Fortunately I was able to get my data all cleaned up late last night, and migrated into (OS X's) Address Book, since I just moved from Palm to the iPhone.

Bob