# "Find" and "Replace" Help Needed

##### New Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### NeonRedSharpie

##### Well-known Member
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.

Code:
``=IF(LEN(Q29)=4,"0"&Q29,Q29)``

As for your second question, yes.

Find: "-*"
Replace:

##### New Member
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.

Code:
``=IF(LEN(Q29)=4,"0"&Q29,Q29)``

As for your second question, yes.

Find: "-*"
Replace:

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

Replies
4
Views
83
Replies
4
Views
95
Replies
0
Views
29
Replies
1
Views
70
Replies
3
Views
59