Cut and remove last text

[Jeffery B Paarsa]

Hello

The texts are comming in the following format:
5121 W Crystal Ln Santa Ana Ca 927041924
I wrote the following to extract the last piece of text which is Zip code,
Option Explicit

Sub LastWD()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim rng As Range
Set ws = Worksheets("United Care")
lastrow = ws.Cells(Rows.Count, "F").End(xlUp).Row

For i = 1 To lastrow
Set rng = ws.Range("F" & i)
ws.Range("G" & i).Value = Right(rng, Len(rng) - InStrRev(ws.Range("F" &
i).Value, " "))

Next

End Sub

After copying the Zip code into G column I would like to remove the Zip code
and let the text in Column F be "5121 W Crystal Ln Santa Ana Ca"

How can I do that?

 

[Jacob Skaria]

Replace the for loop with the below

For i = 1 To lastrow
Set rng = ws.Range("F" & i)
ws.Range("G" & i).Value = Mid(rng, InStrRev(rng.Value, " ") + 1)
ws.Range("I" & i).Value = Left(rng, InStrRev(rng.Value, " ") - 1)
Next

If this post helps click Yes

 

[Cut and remove last text]

Thanks, but it did not work... This what I changed my code to:
Option Explicit

Sub LastWD()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim rng As Range
Set ws = Worksheets("United Care")
lastrow = ws.Cells(Rows.Count, "F").End(xlUp).Row

For i = 1 To lastrow
Set rng = ws.Range("F" & i)
ws.Range("G" & i).Value = Mid(rng, InStrRev(rng.Value, " ") + 1)
ws.Range("I" & i).Value = Left(rng, InStrRev(rng.Value, " ") - 1)
Next


End Sub

Here is what I got after the code ran:
Column F:
5121 W Crystal Ln Santa Ana Ca 927041924
Column G:
927041924
Column F was supposed to have:
5121 W Crystal Ln Santa Ana Ca after removing the zip code..

Regards,

Jeff Paarsa

 

[Cut and remove last text]

Thanks, but it did not work... This what I changed my code to:
Option Explicit

Sub LastWD()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim rng As Range
Set ws = Worksheets("United Care")
lastrow = ws.Cells(Rows.Count, "F").End(xlUp).Row

For i = 1 To lastrow
Set rng = ws.Range("F" & i)
ws.Range("G" & i).Value = Mid(rng, InStrRev(rng.Value, " ") + 1)
ws.Range("I" & i).Value = Left(rng, InStrRev(rng.Value, " ") - 1)
Next


End Sub

Here is what I got after the code ran:
Column F:
5121 W Crystal Ln Santa Ana Ca 927041924
Column G:
927041924
Column F was supposed to have:
5121 W Crystal Ln Santa Ana Ca after removing the zip code..

Regards,

Jeff Paarsa

 

[Jacob Skaria]

Hi Jeffery

In the earlier post I have referenced to ColI change that to F...as below

For i = 1 To lastrow
Set rng = ws.Range("F" & i)
ws.Range("G" & i).Value = Mid(rng, InStrRev(rng.Value, " ") + 1)
ws.Range("F" & i).Value = Left(rng, InStrRev(rng.Value, " ") - 1)
Next

If this post helps click Yes