REMOVE LEADING ZEROS

[Guest]

00456 Tickford Dr.---->456 Tickford Dr.
0785 Fort St.---------->785 Fort St.
000309 Old Rock Dr.-->309 Old Rock Dr.

How do I remove the leading zeros for these addresses?

 

[Guest]

Say your first address is in A1. Use this formula:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","%"),"0"," ")),"
","0"),"%"," ")

This will convert existing spaces to percent signs, then convert zeros to
spaces so it can trim the leading spaces. Then it replaces the spaces with
zeros and the percent signs with spaces. Therefore, if any of your addresses
have percent signs in them for any reason, this wouldn't work. You'd have to
choose another character to change the original spaces to, like ~ or ^ or
somesuch.

There might be an easier way to do this, but this should work.

 

[Guest]

=VALUE(LEFT(A2,FIND(" ",A2)-1))&MID(A2,FIND(" ",A2),255)