Cut and remove last text
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
---------------
Jacob Skaria
"Cut and remove last text" wrote:
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" wrote:
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
---------------
Jacob Skaria
"Jeffery B Paarsa" wrote:
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?
--
Jeff B Paarsa
|