Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text - Remove text Item No.99 (First 2 Chars) and move to end | Excel Discussion (Misc queries) | |||
Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
Filter text in a column by its Indent, to remove certain text | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |