ExcelBanter - Cut and remove last text

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Cut and remove last text (https://www.excelbanter.com/excel-programming/434692-cut-remove-last-text.html)

Cut and remove last text

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

Cut and remove last text

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

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" 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

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