How do I extract a zip code from a column that may have a random amount of text (body of text) in a spreadsheet?

[cmanlongat]

Hi all,

I have a spread sheet with a column that includes a random amount of text like so:

-----------------------


Contact Name:


Contact Email:


Contact Phone:

I am interested in 1234NE 12Tth, Williston, FL 32696.


Search CriteriaCity :
State :
ZIP : 0
MinLotSize : 0
MaxLotSize : 0
MinYearBuilt : 0
MaxYearBuilt : 0
PropertyTypes : CondoApartment, LotsLand, Manufactured, MultiFamily, SingleFamily

-------------------

What I want to do: Extract only the zip code from this column.
My problem: I cannot find a formula to complete this task because every cell is different. Some may display the above posted or some may have double the text, so I am not sure how to pinpoint the formula to do what I want it to do.

Any help would be very much appreciated.

 

[Rick Rothstein]

Will the line of text with the zip code always start with the words "I am interested in"?

Will there always be a period after the zip code?

 

[cmanlongat]

Hi Rick,

Neither, "I am interested in" or a period after the zip code are included in the column. Can this even be done? Thanks.

 

[cmanlongat]

As a matter of fact, yes, a MAJORITY of the text include "I am interested in" following a zip code with the respective state. Hope this helps.

 

[Eric W]

Here's one way:

	A	B
I am interested in 1234NE 12Tth, Williston, FL 32696.

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]32696[/TD]

</tbody>

Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=MAX(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-4)),5)+0,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This is an array formula, confirm it with Control+Shift+Enter when you put it in the formula bar.

This formula will find the largest 5-digit number in the cell. And as such, it has some limitations. If there are other 5-digit numbers, or longer numbers, it might not grab the right one. If you have some odd text, such as 11e11, then it could grab that. But depending on your data, it might work for you. Let me know if it works, or if you think it might need some tweaking.

 

[Rick Rothstein]

As a matter of fact, yes, a MAJORITY of the text include "I am interested in" following a zip code with the respective state. Hope this helps.

Unfortunately, a "majority" is not enough to lock onto... I would need "always" in order to do it. In looking at your data again, would it be correct to say that, excluding blank cells, that every value has a colon in it except for the address value that you want to parse?