How to check if value starts with text

[Omer]

How do I check Cell content starts with text using vba?

I am trying to fix address in column E, if cells in column E starts with text then copy the cell value on the right side column F

Example





Before

+-------------------------+------------------+
| Address ColE | Suite ColF |
+-------------------------+------------------+
| 1440 CAR PKWY | APT 19205 |
| 69 BELLA | |
| 860 VALLEY PRKY | APT 2094 |
| 200 CENTURY CENTER BLVD | SUITE100 |
| ONEWAY BLVD | 7700 ONEWAY BLVD |
+-------------------------+------------------+

After


+-------------------------+------------------+
| Address ColE | Suite ColF |
+-------------------------+------------------+
| 1440 CAR PKWY | APT 19205 |
| 69 BELLA | |
| 860 VALLEY PRKY | APT 2094 |
| 200 CENTURY CENTER BLVD | SUITE100 |
| 7700 ONEWAY BLVD | |
+-------------------------+------------------+


Can someone help me with vba example, on how to do this- Thanks

[SamT]

This checks only the first character, but "3rd Ave." will fail

If not IsNumeric(Mid(Cel, 1, 1)) Then

This is longer,but checks the entire first "word."

Dim sp as Long
sp = InStr(Trim(Cel), " ")
If Not sp Then ' No space = only one word = no numbers
Cel = Cel.Offset(,1)
Else
If Not IsNumeric(Left(Cel, sp)) Then Cel = Cel.Offset(, 1)
End If

[Omer]

Thank you, SamT