More efficient (faster) sub-String method?

[Bytor47]

I have a piece of code that works very well but takes a long time. 1s per instance to be exact. My data is going to approach 3000 records so that translates into almost an hour to run through the entire data set.
I am looking for code that will produce the same results but in much less time. Is it possible?

Basically I have a Column that contains names with English and French acronyms separated by [space]/[space]. I need just the English acronym.

Group Name:
ABCDEFG / ABCGDF
XYZ / ZXY
ABC123 / CAB123

Changes to....

Group Name:
ABCDEFG
XYZ
ABC123

This is the code I am using:

Please Login or Register  to view this content.

[Mordred]

How about amending the following to suit the range you want to go through

Please Login or Register  to view this content.

It places the split string to the right of each cell it loops through. I think this should be quicker.

EDIT: I just tested this on about 25,000 rows and it finished in about 3 seconds.

[Norie]

You might want to try Data>Text to columns... with / as a delimiter and skipping the second column.

I just tried that on 15000 rows of data similar to yours, it was quite quick.

[Mordred]

You just have to love the multiple ways of doing things with Excel.

[Bytor47]

Thanks to all for the quick responses.

This has confirmed my suspisions about my working environment. I tried Mordred's code and it achieves the same results as what I had but in the same amount of time; slow. We don't use local installations of Excel on our machines at work so I am sure that is what is causing my slow processing times.

Norie's solution works but I need to do a search&replace first to account for some exceptions but in the end it still works really fast compared to the other methods.

Looks like I can get the 1 Hour processing time down to a few minutes.

As always, I appreciate the help.

[jindon]

Try this

Please Login or Register  to view this content.