CODEWITHSUNDEEP
vbaexcelreplaceexcel-2010
0m3r
0m3r

Reputation: 12499

Remove a specific word and everything after in a Cell

I'm having trouble deleting everything after and including the word APT and STE on Address column

If you look at the result blow, my vba code is deleting the word that the letters ste ( 2121 STEVENSON LN) or apt.

What is best way to to remove the word APT or STE and everything after that?

Below is my code,

Option Explicit
Sub Remove()
    Dim Sht As Worksheet

    Set Sht = ActiveWorkbook.Sheets("Data")
    With Sht.Range("E:E")
        .Replace "APT*", "", xlPart
        .Replace "STE*", "", xlPart
    End With

End Sub

My Data

+-------+-----+-----+----------+----------------------------------+-------+------+-----+
| Route | Pcs | Wgt | Location |             Address              | Suite | City | Zip |
+-------+-----+-----+----------+----------------------------------+-------+------+-----+
| SD-26 |   1 |   3 |          | 5555 SOUTHWESTERN BLVD           |       |      |     |
| SD-26 |   1 |   7 |          | 6666 EASTERN AVE APT 100         |       |      |     |
| SD-05 |   1 |   1 |          | 161112 HOMESTEAD ST              |       |      |     |
| SD-05 |   2 |   8 |          | 2221 STEVENSON LN                |       |      |     |
| SD-04 |   1 |   8 |          | 4040 OLD DENTON RD APT 2104      |       |      |     |
| SD-04 |   1 |   3 |          | 15811 E FRANKFORD RD APT 1507    |       |      |     |
| SD-04 |   1 |   1 |          | 835 WESTMINSTER DR               |       |      |     |
| SD-03 |   1 |   5 |          | 9001 LAKESIDE CIR APT 5203       |       |      |     |
| SD-03 |   1 |   3 |          | 8880 UNION STATION PKWY APT 2104 |       |      |     |
| SD-03 |   1 |   1 |          | 420 E MAIN ST STE E              |       |      |     |
+-------+-----+-----+----------+----------------------------------+-------+------+-----+

Result 

+-------+-----+-----+----------+--------------------------+-------+------+-----+
| Route | Pcs | Wgt | Location |         Address          | Suite | City | Zip |
+-------+-----+-----+----------+--------------------------+-------+------+-----+
| SD-26 |   1 |   3 |          | 5555 SOUTHWE             |       |      |     |
| SD-26 |   1 |   7 |          | 6666 EA                  |       |      |     |
| SD-05 |   1 |   1 |          | 161112 HOME              |       |      |     |
| SD-05 |   2 |   8 |          | 2221                     |       |      |     |
| SD-04 |   1 |   8 |          | 4040 OLD DENTON RD       |       |      |     |
| SD-04 |   1 |   3 |          | 15811 E FRANKFORD RD     |       |      |     |
| SD-04 |   1 |   1 |          | 835 WESTMIN              |       |      |     |
| SD-03 |   1 |   5 |          | 9001 LAKESIDE CIR        |       |      |     |
| SD-03 |   1 |   3 |          | 8880 UNION STATION PKWY  |       |      |     |
| SD-03 |   1 |   1 |          | 420 E MAIN ST            |       |      |     |
+-------+-----+-----+----------+--------------------------+-------+------+-----+

Upvotes: 1

Views: 181

Answers (1)

user4039065
user4039065

Reputation:

Seems to me you can just include a space before and after APT/STE but before the wildcard character.

Sub RemoveAptSte()
    Dim Sht As Worksheet

    Set Sht = ActiveWorkbook.Sheets("Data")
    With Sht.Range("E:E")
        .Replace " APT *", vbNullString, xlPart
        .Replace " STE *", vbNullString, xlPart
    End With

End Sub

That should remove just about any false positive from consideration.

  apt_ste

Upvotes: 3

Related Questions