"Find" and "Replace" Help Needed

[traffictrader]

Hi Everyone,


This is my first post here on the forum.

As a novice user of Excel, it is so great this forum exists.


Thanks in advance for any help.


I have a large spreadsheet of US zip codes that needs to be cleaned up.


Question #1:


Some are in the 5 digit format (which I need) Eg: 90210 and some are in the 4 digit format eg: 4536


Is there a way to add a zero in front of all the 4 digit format zip codes using the Find and Replace function? Or is there some other way to do this?




Question #2:


The US Post Office sometimes places a 4 digit extra code after the standard 5 digit code Eg: 90210-9876


Is there a way to easily delete all the 4 digit extra codes and just keep the standard 5 digit codes?


Thanks again for any help on this.


Cheers,


Ralf

 

[NeonRedSharpie]

To your first question....not really. Unfortunately for zip codes, excel treats them as a number. So it will trim the leading zero. If you need it to LOOK like it has the leading zero, you can do that with custom formatting/conditional formatting. If you need it to have the leading zero, you'll have to convert the numbers to text. Or, on the import, import them as text. But you can use a simple formula if you can spare a helper column.

=IF(LEN(Q29)=4,"0"&Q29,Q29)

As for your second question, yes.

Find: "-*"
Replace:

 

[traffictrader]

To your first question....not really. Unfortunately for zip codes, excel treats them as a number. So it will trim the leading zero. If you need it to LOOK like it has the leading zero, you can do that with custom formatting/conditional formatting. If you need it to have the leading zero, you'll have to convert the numbers to text. Or, on the import, import them as text. But you can use a simple formula if you can spare a helper column.

=IF(LEN(Q29)=4,"0"&Q29,Q29)

As for your second question, yes.

Find: "-*"
Replace:

Thank you NeonRedSharpie...I will give this a try.