Omit Data from Address

[steigrafx]

I'm having a heckuva time with this, and am hoping someone can lead me in the right direction.

I need to be able to remove everything in parentheses on the first line of an address. For example:

A-A ADL Transcription Services, Inc. (East Islip)

3132 Union Boulevard

East Islip, NY 11730

should display in another field as

A-A ADL Transcription Services, Inc.

3132 Union Boulevard

East Islip, NY 11730

The entire business name and address are contained in one field called Service_Address. I can't figure out how to write the calculation.

Thanks for any help you can provide.

Kevin

[Fitch]

Someone's probably already made a function for this on briandunning.com, but here's a quick calc for ya:

Let([

  txt = yourAddressField ;

  par1 = Position ( txt ; "(" ; 1; 1 ) ; 

  par2 = Position ( txt ; ")" ; 1; 1 ) ; 



  result = Replace ( txt ; par1 ; par2-par1+1 ; "" )

  ];



  result 

 )

[comment]

Is the thing in parentheses always at the end of the the first line?

[steigrafx]

Yes, it is always at the end of the first line.

[steigrafx]

Thank you, Tom. It works like a charm.

Have a great weekend.

Kevin

[steigrafx]

Tom, I just realized that there is one small flaw.

If the first line of the address does not contain parentheses, the calculation cuts off the first letter of the first line.

For example,

Aria Transcriptions

102 Sparrow Ridge Road

Carmel, NY 10512

becomes

ria Transcriptions

102 Sparrow Ridge Road

Carmel, NY 10512

Can the calculation be easily adjusted to ignore an address without parentheses?

Thanks again,

Kevin

[comment]

Try =

Let ( [

line1 = GetValue ( Address ; 1 ) ;

start = Position ( line1 ; "(" ; 1 ; 1 ) ; 

end = Length ( line1 ) 

] ; 

Case ( start ;

Replace ( Address ; start ; end - start + 1 ; "" ) ;

Address

) 

)

Note:

This leaves in the character preceding the opening parenthesis. If you can be sure it's always a space or another punctuation mark, change the Replace() part to:

Replace ( Address ; start - 1 ; end - start + 2 ; "" ) ;

Edited July 16, 2011 by comment

[steigrafx]

Thank you, comment. Yes, it's always a space preceding the parenthesis. I used your calculation and changed the "Replace" line as suggested by you and it now works perfectly.

Thanks again, and have a nice weekend.

Kevin