Adding Trailing Zeroes

[jomili]

I need a macro to do a comparison between two spreadsheets, and one part of it is throwing me, as I need to compare apples to oranges. Here's the situation:

Sheet A has the data noted like this:
PAC
11000
11100
11500, etc.
All data should have 5 digits, and my numbers range from 11000 to 87028, though quite a few are skipped.

Sheet B has data like this:
PAC
110
110-CBS
11000
11100-ING
112
11500-L

How can I change sheet B to:
1) Drop off the dash and anything following?
2) Add trailing zeroes to make sure all numbers are 5 characters in length?


Thanks in advance for any help. I don't have any code to poste on this because I'm just about to start writing it, but need this part first.

[MickG]

Hi, This is possible one way !!

Please Login or Register  to view this content.

Regards Mick

[mikerickson]

Please Login or Register  to view this content.

[jomili]

Wow! You guys rock!

MickG, your solution worked right off the bat, quick and pretty. Thanks loads!

MikeRickson, your solution halfway worked. It DID remove all of the stuff from the dash on, but it did NOT change the 3-digit numbers (like 110) to 5 digit (like 11000).

I'll mark this question solved, and thank you both for being so quick. Mike, if you have any idea why your solution only halfway worked I'd be interested in hearing it.

Thanks, and Happy New Year!