Combine vba codes

[mmwltd]

Hi
I have large text files to convert and I would like to get all the codes combined into one, if thats possible.

The text i want to parse has various quantity of 7 character partnumbers at the end. one piece of code splits the string out and the following formula can remove all the pieces split out from the original string so basically i have the fully original string and then the string split out into the individual parts.

I cant find the vba for this formula but i have placed it below where it should run

=LEFT(A1,SEARCH(B1,A1)-1)

Can somebody take a look at that and maybe combine the formula and the following code into one piece?

The code is in the order I'd like it performed - with the formula inserted where it should be.

The one issue i found while testing the individual pieces is that most of them require the columns to be manually selected which i would prefer to happen automatically

(I've found all this vba on the net)

Thanks
John

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JBeaucaire]

If you post a sample workbook showing a few rows of the BEGINNING DATA and then a mockup of those same rows after your macros have run showing your desired END RESULT, this would be simplest to offer the best answer.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[mmwltd]

Hi

I've attached a few sample lines of beginning data and end result required.

Thanks

[JBeaucaire]

This should do it:

Please Login or Register  to view this content.

[mmwltd]

Thanks for that. I've tried it on a small sample of data and it works great. I've noticed that it doesn't to CLEAN the text (I don't know if it TRIMs it)
Will it still work if i use formulas to CLEAN and TRIM the text first or should i run the vba then CLEAN and TRIM?

I just want to make sure i dont mess things up because it'll be very difficult to see if something goes wrong until a customer complains!

Thanks again
John

[JBeaucaire]

Replace that one macro with these two, the second Function is used by the first to properly clean out any hidden junk.

Please Login or Register  to view this content.

[mmwltd]

Replace that one macro with these two, the second Function is used by the first to properly clean out any hidden junk.

Because all this is new to me I have what is probably a very dumb question

I copied and pasted the original macro into excel so do i just copy and paste these two over the original or do i need to save the two new macros in separate files?

Thanks

[mmwltd]

Hi

Is there a way to limit the CLEAN portion of this macro to just unprintable characters and extra spaces because some of the text has "-" and "@" that need to stay.

Thanks

[JBeaucaire]

in the CLEANALL function, add the characters you want allowed into the match pattern. Be sure not to delete the blank space in there:

Please Login or Register  to view this content.

[mmwltd]

Thanks very much for all your help