I have a database which I imported but it had a ":" in each record.

I would like to delete everything to the left of and including the ":" in each of these records. How can I make a script to do this?

The example is

004 Toronto, Ontario

I would lIke this field to only say

Toronto, Ontario

Before you try this, make a copy of your file.

Then, in browse mode, click in your field, goto Records Menu --> Replace field contents, choose replace with calculated results and specify this as your calc:

MiddleValues(

Substitute(YourFieldHere ; ":" ; "¶")

;2;1)

Where YourFieldHere is your field

~Genx

I didn't think you asked to have a return placed in your fields - at the END of your field. And you probably want excess spaces removed. You can try this as well:

TrimAll (

Let (

anchor = Position ( text ; ":" ; 1 ; 1) ;

Middle ( text ; anchor + 1 ; Length ( text) )

) ; 0 ; 0

)

There are a hundred ways to write this...

Actually, TrimAll() isn't necessary unless you suspect your data might have extra spaces inbetween. If you're sure it's clean, you can shorten it to:

Let (

anchor = Position ( text ; ":" ; 1 ; 1 ) ;

Trim ( Middle ( text ; anchor + 1 ; Length ( text) )

)

)

Let() isn't needed but I like it. The first calc removes extra spaces throughout the string. :wink2:

Does it really matter if a return gets placed in the field? I mean we filter for the second value only anyway, unless this is a speed issue..

~Genx

Genx, the RESULT value from your calc produces:

(sp) (sp) Toronto, Ontario (carriage return)

I wouldn't want that result in MY fields. Do you test your stuff before you respond?

Speed aside, you had the idea but it was messy. From a speed standpoint, every evaluation counts. If this was Replace Contents through 600,000 records yes. I have record-counts this size and so do many others. And this import may be a weekly import for all we know. Over time, that could add up to a lot of lost time waiting for a process ...

While you're at it, add one more sin to the list, and that is the sin of assumption.

What if the next record has "005 Montreal, Quebec¶Canada"? Assuming there are no carriage returns in the field is OK - but it needs to be pointed out.

The sin of assumption has indeed now been added.

I realized I also might have mentioned that, if this is on ongoing import-need then this could be an Auto-Enter (Do NOT Replace) on the new field (leaving the original import field intact for an audit trail). And that a further, proper break-out of the address might be in order. Why do it later when now would be an opportune time.

I would like to delete everything to the left of and including the ":" in each of these records. How can I make a script to do this?

Use this calc to set the field to itself:

Replace ( entry ; Position ( entry ; ":" ; 1 ; 1 ) ; 999999 ; "" )

But autoformatting like LaRetta suggests, slows down the import considerably ...but perhaps it's worth it??

--sd

Ahem... is that your left or mine?

I don't understand???

--sd

Input: "004 Toronto, Ontario"

Desired output: "Toronto, Ontario" (delete everything to the LEFT of and including the ":" )

Your result: "004 " (deleting everything to the RIGHT of and including the ":" )

LOL - correct!

I took the liberty to consider the just after the ":" - junk'ish as well.

Replace ( entry ; 1 ; Position ( entry ; ":" ; 1 ; 1 )+1 ; "" )

--sd

Hmmm, sigh, righto, cut down evaluations, don't make assumptions ... got it...

Cheers

~Genx

Might not

Trim(Replace ( entry ; 1 ; Position ( entry ; ";" ; 1 ; 1 ); "" ))

be a bit safer?

Good point indeed!

--sd