I have a text field in a database with 1200 records containing 3 lines of text:

First Name Last Name

Street

City, State, Zip

I need to extract just the Street name which has variable number of words of different lengths. What text function or script would be best to use?

Thanks

Welcome to Forums!!

Try this:

MiddleValues ( textField ; 2 ; 1 )

If you wish to set another field with this value, you can use Replace Field Contents (if not being served at the time) or script. If Replace Contents, just enter this calc in the 'Replace with Calculated Result' after putting your cursor in the new field. If by script, use:

Go To Record/Request [ First ]

Loop

Set Field [ newField ; MiddleValues ( textField ; 2 ; 1 ) ]

Go To Record/Request [ Exit after Last ; Next ]

End Loop

LaRetta :wink2:

Well ... that was messy of me. It leaves a hard return at the end so you'll need to remove it. Sorry for my error - I didn't expect that MiddleValues() wouldn't clean itself up ... and we don't want to leave junk in your fields!!

Substitute ( MiddleValues ( textField ; 2 ; 1 ) ; ¶ ; "" )

Thank you so much. The MiddleValues ( textField ; 2 ; 1 ) function worked beautifully. What a great resource this Forum is.

Ciao for Niao

Ummm, so do you now need to remove those carriage returns? :blush2:

I don't need to for this particular task, but would appreciate knowing how. Thanks again.

Well you could simply run my second calc through again on the new field. Or - JUST to remove carriage return at the end, try:

Substitute ( newText ; ¶ ; "" )

And yes, I agree FM Forums is an excellent resource. But as I've just proved, it's recommended that one backs up first before applying ANY process such as this.

Just general information ... with vs. 8 there is a more effective method than MiddleValues(). This would have ALSO removed the carriage return at the end. I see your on vs. 7 but I wanted to remind everyone about this new, wonderful function.

GetValue( textField; 2)

LaRetta