Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Splitting a field's contents


Tosh

Recommended Posts

Hi All

As part of a database tidy up I am trying to split the contents of a field into 2. ie I have a (text) field (called quote ref) that contains 1234-1 (different in each record) and I need to leave the first 4 digits (1234) where they are, remove the dash alltogether and put the 1 (after the dash) into another (number) field. I have to do this for about 6000 records. If anyone can help that would be fantatstic!

Link to comment
Share on other sites

First ...Make a good backup of your database....just in case.

 

I presume there is always 4 digits then a dash and a digit.

I don't like to assume because you know the rest.

And the second number field is blank. If not script will still work.

 

Now the script

 

Set Field [Field2] Field2 & Right(Field1;1)

##Settting field2 contents of field2 and number after the dash ##

Set Field [Field1] Left(Field1,4)

 

 

After you have proven it on a few records... then add

 

Loop

Go to record request [first]

##Above script goes here ##

Go to record request [next] [exit after last]

end loop

 

Again ...Make a good backup of your database....

Before you run this because it will change your data for good.

Link to comment
Share on other sites

I agree with Chopper. The ONLY thing we can guarantee is that the dash is the separator, ie, might there be a number 12 after the dash? So I suggest using that as the split point instead of depending upon the length of characters on either side. And a space at beginning or end will also skew your parse and will result in dropped characters.

 

Prior posts indicate vs. 7 so the following would work and be a bit safer. Just use them in your Set Field[] calculations:

 

Grab left side: Trim(Middle(text ; 1 ; Position(text ; "-" ; 1 ; 1) - 1))

 

Grab right side: Trim(Middle(text ; Position(text ; "-" ; 1 ; 1) + 1 ; 99))

Link to comment
Share on other sites

What can I say, you're both legends!

Job done... I did have a few double digits after the dash, but just omitted them before running the script.

Thanks

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use