Jump to content

delete in field everything to left of ;


This topic is 6570 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 ...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

The sin of assumption has indeed now been added. :wink2:

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6570 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.