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
|
Similar Threads
