CODEWITHSUNDEEP
excel
user44252
user44252

Reputation: 43

Excel: How to copy specific cell data (zip codes) into a new column

I have two columns of cells that have irregularly formatted addresses.

I need:

1) just the zip codes to be copied into a new column;

2) the rows that do not contain zip codes to be either highlighted or empty so that I can easily identify which ones are missing.

This seems like it would be simple to do, but I can't figure out how to have Excel just find all instances of 5 consecutive numbers. Currently they are formatted as text so that the zero's are displayed. Any help greatly appreciated.

Here's what it would be to start with: enter image description here

Here's what it would look like when done (highlighting optional): enter image description here

Upvotes: 1

Views: 1132

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Another approach to what Zoff Dino wrote is to break it out a bit as shown below:

  1. In cell C3 enter the formula you see in the formula bar
  2. Drag that down the row set and over 1 column (so it runs for column B as well)
  3. In column use this formula: =IF(AND(C3="",D3=""),"",IF(C3="",D3,C3)) and drag it down.

This will account for all possible situations you have shown and not error out on you (unless other patterns emerge).

enter image description here

You can then use conditional formatting to highlight the rows with no zip code as shown in the picture:

enter image description here

Upvotes: 0

Code Different
Code Different

Reputation: 93181

You don't have Regular Expression in normal Excel. You would have to go into VBA to do that. However, for your case, there's an easy pattern: notice how the zip code is after the last space, and it's always 5 digits long? The challenge then become finding the index of this last space and extract the 5 characters that follow it. It will be clearer if you split them into 2 formula

// C3 (index of last space character):
=FIND("|",SUBSTITUTE(B3," ","|",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))

// D3, the 5 characters after that.
// Return an empty string if the address doesn't match the pattern
=IFERROR(MID(B3,C3+1,5),"")

Upvotes: 1

Related Questions