Extracting Field Content

[Moosh]

Hi,

I have a field with content as follows

"505 Woodland Terrace - 3"

Which is a Property Address + the Unit. Is there a way I can extract the content of the address and the unit, i.e., use the "-" as the point where the data is split? So I'll have 2 other fields that read this one, and the first would output all the content before the "-" and the second field would have all the content after "-"?

Thanks!

Moosh

[LaRetta]

Hi Moosh,

[color:black]To get the address

Let(

dash = Position ( theField ; "-" ; 1 ; 1 ) ;

Trim (

Middle ( theField ; 1 ; dash - 1 )

)

)

[color:black]To get the unit

Let(

dash = Position ( theField ; "-" ; 1 ; 1 ) ;

Trim (

Middle ( theField ; dash + 1 ; Length ( theField ) )

)

)

You can use formulae in text calculation, script setting new fields (using Set Field[]) or via Replace Field Contents (on new fields). Back up first.

LaRetta

[Moosh]

Perfect! Thanks so much!!

[LaRetta]

I should warn you that if theField has a dash within the address portion (ie, more than one dash), it will break. It might be safer to 1) search your field and make sure you don't have more than one dash. You can create a calculation (number) with: PatternCount ( theField ; "-" ) and search on this calculation for > 1 or ... modify the calcs I gave you to only look for the LAST dash within the field.

[Moosh]

very good point... i changed the separation to "unit" instead of a "-" and modified the scripting you included to create the same result. this is a lot more universal... Thanks for the advice!

[LaRetta]

It is much easier to combine via text calculation than to break real data apart within a field. You might consider KEEPING them in separate fields and then just creating a calculation to concatenate them. It would be simple: Address & " - " & Unit. Because, the first time you think you have it straight, someone will type 'unit' into the address and it'll break the next time you attempt to split them ...