Formula to Remove characters from a cell

[RLJ]

I would like a formula to remove a possible zip code from a cell. I get data from a Client Database and want to remove the zip code from the cell as it is duplicated in another cell. All I want is the City/State.

Here is my problem... It is a client database so I do not have control over how the information is input. The field could inculde the zip+0000, just the zip, a blank field or it could be another part of the address. As indicated below.

Excel 2010

	I
265	NEW YORK, NY 10020
266	LOS ANGELES, CA 90067-3100
267	NEW YORK, NY 10022-2000
268	NEW YORK, NY 10022
269	NEW YORK, NY 10022
270	NEW YORK, NY 10166
271	NEW YORK, NY 10166
272
273	25 MAIN STREET HACKENSACK

<COLGROUP><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Additional Contacts​

As I see it the formula needs to look for a cell with a comma in it and then after 4 characters to the right remove it, and if no comma exisits, then copy original cell as is.

Thanks for your help!

 

[Yahya]

Did you try it on the OP's last example...

25 MAIN STREET HACKENSACK

i didn't
i thought all zip code at the end

 

[RLJ]

I ended up going with the VBA Function. It did not seem like the formulas presented here removed the zip from the string.

Thanks for all your help! MrExcel is a great community.

 

[Rick Rothstein]

I ended up going with the VBA Function. It did not seem like the formulas presented here removed the zip from the string.

I would be interested in seeing the text string which made the formula I posted in Message #8 not work... would you please post it back to this thread for me? Thanks.

 

[István Hirsch]

In the line

.Pattern = "[-\d\s]+$"

please replace + with * to treat HACKENSACK.