Extracting Data in Multiple Cells

Hello All,

I have a question that I am not sure if it can be solved or not by a formula.

My data set looks like this

123 Street	Fake Town, CA	12345
Mr. Bob	456 Road	Town, PA 10213
456 Blvd	City, FL 12032
Mr. Smith	1231 Ave	Village, NY 102021234

What I need to do is in the last column, show just the zip code

Final Data set looks like this.

123 Street	Fake Town, CA	12345	12345
Mr. Bob	456 Road	Town, PA 10213	10213
456 Blvd	City, FL 12032		12032
Mr. Smith	1231 Ave	Village, NY 102021234	102021234

Any recommendations?



Thank you for all the responses below. As I started working on my data, I was wondering if the following was possible as well?

Finished Data Set

123 Street	Fake Town, CA		12345
Mr. Bob	456 Road	Town, PA	10213
456 Blvd	City, FL		12032
Mr. Smith	1231 Ave	Village, NY	102021234

Try this user defined function

Please Login or Register  to view this content.

Switch to the VBA editor (Alt F11), Insert a new module (Insert- Module) and paste in the above. Alt F11 back to worksheet.

In A4 type =GetZipCode(A1:C1) and copy down.

Assume the data is in A1:C1, in D1

=TRIM(RIGHT(SUBSTITUTE(LOOKUP(2,1/(A1:C1<>""),A1:C1)," ",REPT(" ",20)),20))

copy down.

Hello
I used formula's here.with a helper column..
If this is applicable..

extractdata.xlsx

HTH

Hello jdot,

Here is another formula you can use. This will work as long as there are numbers present in the cell. If no numbers are found then you will see #VALUE in the cell. This formula assumes cell with the Zip code in is column "C".

=RIGHT(C1,LEN(C1)-SMALL(IF(ISERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},C1)),256,SEARCH({0,1,2,3,4,5,6,7,8,9},C1)),1)+1)