"Find" and "Replace" Help Needed

traffictrader

New Member
Joined
Oct 3, 2014
Messages
2
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
 

Some videos you may like

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
Joined
Jul 14, 2014
Messages
1,678
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:
 

traffictrader

New Member
Joined
Oct 3, 2014
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top