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!

 

[ChrisR]

Hi,

Assuming you're using Excel 2007 or later

=IFERROR(LEFT(G5,LEN(G5)+3-FIND(",",G5)),G5)

Just change G5 for you cell reference

Hope this helps

Chris

 

[Jonmo1]

Try

=LEFT(I265,FIND(",",I265&",")+3)

 

[RLJ]

Thanks, Works just what I needed!

 

[RLJ]

Spoke a little too soon. The formula works, but can it be changed to read after the last comma in the cell. I just came across one that is New York,, NY 10022-3598. So with Jonmo1's equation I ended up with New York,, N

Thanks for your help!

 

[István Hirsch]

This works for the sample provided.
Press Alt+F11, copy the code into an empty module, check Microsoft VBScript Regular Expressions 5.5 option_
at Tools/References then use this as a User Defined Function.

Function DeleteZip(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[-\d\s]+$"
        If .Test(c) Then DeleteZip = .Replace(c, "")
    End With
End Function

 

[markmzz]

Maybe this formula:

=REPLACE(B1,FIND(C1&"X",B1&"X"),20,"")

Where C is the column of the zip code.

Markmzz

 

[Rick Rothstein]

This formula should work for you...

=IF(ISNUMBER(-RIGHT(I265)),LEFT(I265,LEN(I265)-LEN(TRIM(RIGHT(SUBSTITUTE(I265," ",REPT(" ",99)),99)))-1),IF(LEN(I265),I265,""))

 

[Yahya]

test this formula also
=LEFT(I266,LEN(I266)-LEN(MID(I266,MIN(FIND({0,1,2,3,4,5,6,7,8,9},I266&"0123456789")),255)))

 

[Rick Rothstein]

test this formula also
=LEFT(I266,LEN(I266)-LEN(MID(I266,MIN(FIND({0,1,2,3,4,5,6,7,8,9},I266&"0123456789")),255)))

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

25 MAIN STREET HACKENSACK