open xml file in Excel

J

Joe

I'm using excel to open an XML file of address, but it truncates the leading
zero if a zip begins with one. I don't have the option of modifying the
source file and putting in an apostrophe. Is there a way around this?
 
T

Tyro

If the zip is in its own column, A for example, you might use a helper
column. For a zip such as 1234 which should be 01234 you could use a formula
like
=IF(LEN(A1)=4,"0"&A1,A1). For a zip such as 1234-5678 You could use
=IF(LEN(A1)=9,"0"&A1,A1)
The results of both formulas will be text. If you're satisfied with the
results, you can delete column A and use the helper column as the zip code.

Tyro
 
Top