VBA function to remove hypens, spaces, leading zeros

Hi,
I have searched but can't seem to find exactly what I need to accomplish this simple task. I want to
1. Strip all leading spaces (could be any number of spaces) e.g. " 12345" or " 12345"
2. Strip all leading zeros (could be any number of zeros) e.g "012345" or "00012345" or " 012345"
3. Strip all spaces in the string (could be anywhere in the string and any number of spaces) e.g. "12 34 56 " or " 1"
4. Strip all Hyphens in the string (could be anywhere in the string and any number of hyphens)

I've managed to cobble this code together that does most of what I need but it wont handle the case where I have a leading space and a leading zero. (e.g. " 012345"). In this example the leading space gets removed but the zero doesn't. I've tried adding a "Trim" but I just can't get it to work.

What code do I need to add to remove the leading space where there is also a leading zero and where do I need to add it?
My Code

Please Login or Register  to view this content.

Have you thought about adding a value that is " 0", ""?

Have you thought about adding a value that is " 0", so you get

Please Login or Register  to view this content.

Have you thought about adding a value that is " 0", so you get

Please Login or Register  to view this content.

Removed as duplicate posting

Hi ssjody,

I use a formula I got from someone on here to remove trailing spaces from cells normally, I'm not sure if you could adapt it somehow into your macro?

Formula:

Please Login or Register  to view this content.

Thanks

Chriz

Thanks, I tried to get something similar to work but I'm to much of a novice to have any success. I'm pretty sure my problem is that since the space precedes the zero that i only gets a length of one. I've tried moving the "mid" to occur after the replace but I can't figure out the syntax to get it to compile.

Originally Posted by Chriz

Hi ssjody,

I use a formula I got from someone on here to remove trailing spaces from cells normally, I'm not sure if you could adapt it somehow into your macro?

Formula:

Please Login or Register  to view this content.

Thanks

Chriz