Move text

P

pattlee

Data is in Column G As City,State,Zip Need a Macro that will move City to Col
E and the State to Col F. and leave the Zip in the Col G.... Can't seem to
get the ofset parameter correct..or get rid of the comma between City,State
Would appreciate any help Regards Patt
 
R

Ron Rosenfeld

Data is in Column G As City,State,Zip Need a Macro that will move City to Col
E and the State to Col F. and leave the Zip in the Col G.... Can't seem to
get the ofset parameter correct..or get rid of the comma between City,State
Would appreciate any help Regards Patt
Click to expand...

Here's one way that might work.

Note that I hard coded the range to check in Col G. There are many ways that
this could be set up, depending on how your data is organized. But this should
get you started.
--ron
 
P

pattlee

Hi Ron read your message but fail to see where the code is .. Am I missing
something? Patt
 
P

Paul Mathews

Pat, I'm not certain if you need a macro but you can use regular Excel string
functions to accomplish the text parsing you need (you may need a macro if
you intend to perform this parsing action repeatedly with different data).
I'll provide the string function approach first. Let us know if you
absolutely need a macro and I or another party will provide the code.

First, move your original City,State,Zip data column to another column
location. Let's say you place it in column H with the first data item
occuring at H2 (i.e., header row is in row 1). Now, set cells E2, F2 and G2
as follows (exclude the double-quotes around the expressions):

E2: "=LEFT(H2,FIND(",",H2)-1)"
F2: "=MID(H2,FIND(",",H2)+1,2)"
G2: "=RIGHT(H2,5)"

So, for example, if cell H2 contains the text, "Manasquan,NJ,08736", then E2
will contain the text "Manasquan", F2 will contain "NJ", and G2 will contain
"08736". I'm presuming that you're using comma delimiters only (not comma
followed by space) and that all zip codes are represented in the usual five
numeric character format. Copy E2, F2, and G2 down as far as you need for
your data. If you want to delete the original unparsed data, be sure to copy
and paste-value the expressions in columns E, F and G. Hope this helps.

Paul
 
R

Ron Rosenfeld

Hi Ron read your message but fail to see where the code is .. Am I missing
something? Patt
Click to expand...

Oops:

===========================
Option Explicit
Sub ParseCityStateZip()
Dim c As Range
Dim sTemp
For Each c In Range("G1:G100")
With c
If Len(.Text) - Len(Replace(.Text, ",", "")) = 2 Then
sTemp = Split(.Text, ",")
.Offset(0, -2).Value = sTemp(0)
.Offset(0, -1).Value = sTemp(1)
.NumberFormat = "@"
.Value = sTemp(2)
End If
End With
Next c
End Sub
============================
--ron
 
P

pattlee

Paul, The reason I wanted a macro is because this will be repeitive process
on this set of data (possibly five to six different runs) and other similar
sets of alphanumeric data. . The project I am involved in is moving data
from my commercial accounting program into Excel, rearranging the data so
that it is exportable to a new accounting program. There is a tremendous
amount of "data cleansing" and the goal is to keep from manually entering the
data to the new accounting package. We currently use Excel outside of either
accounting programs but have not had the need to write code or use many
macros. Especially in reformatting data. Hopefully I will learn by these
examples. thanx in advance and I certainly appreciate your help.
 
P

Paul Mathews

Hi Pat,

The code below while not the most elegant, should get you what you need (be
sure to make a copy of your original data since the parsed zip code data will
overwrite the original, unparsed data in column G). Note that, before you
run the macro, you need to set focus on the very first data record that you
want to parse in column G. The macro will iteratively run down the data list
and generate the parsed data in columns E, F and G as you need. Column G
should not contain embedded blank data items (if it does, the macro will stop
when it hits the blank record and you'll need to set focus on the next
non-blank data item and run the macro from that point forward; if you have no
embedded blank data records, the macro will stop after the last data record).

Paul

Sub ParseAddress()

'Parse comma-delimited city,state,zip data
Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value,
Application.WorksheetFunction.Find(",", ActiveCell.Value) - 1)
ActiveCell.Offset(0, -1).Value = VBA.Mid(ActiveCell.Value,
Application.WorksheetFunction.Find(",", ActiveCell.Value) + 1, 2)
ActiveCell.Value = "'" & VBA.Right(ActiveCell.Value, 5)
ActiveCell.Offset(1, 0).Select
Loop


End Sub
 
P

pattlee

Thanks ron will try and let you know Regards patt
Ron Rosenfeld said:
Oops:

===========================
Option Explicit
Sub ParseCityStateZip()
Dim c As Range
Dim sTemp
For Each c In Range("G1:G100")
With c
If Len(.Text) - Len(Replace(.Text, ",", "")) = 2 Then
sTemp = Split(.Text, ",")
.Offset(0, -2).Value = sTemp(0)
.Offset(0, -1).Value = sTemp(1)
.NumberFormat = "@"
.Value = sTemp(2)
End If
End With
Next c
End Sub
============================
--ron
Click to expand...
 
P

pattlee

Thanks Paul Alos have a solution from Ron R . You have no idea how much this
means to us newbies.... will try both and be back with the kudos. for
both..... thanks
 
P

pattlee

pattlee said:
Thanks ron will try and let you know Regards patt
Click to expand...

Hi Ron I ran the code on this data and nothing was changed... No results so
thought I would show the exact data and see what I was missing...I have may
have mislead as to the data having two commas, only 1. Please advise...
Regards patt

E F GJACKSONVILLE, FL 32224
CHARLESTON, SC 29405
HIALEAH, FL 33016
OCALA, FL 34478-3200
RICHLAND, WA 99352
MIAMI, FL 33138
 
P

pattlee

Hi Paul, Ran this code and during Debug got error message at line
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value,
Please advise... Patt Am including a few lines of actual Data .
E F G
City STATE ZIP
JACKSONVILLE, FL 32224
CHARLESTON, SC 29405
HIALEAH, FL 33016
OCALA, FL 34478-3200
RICHLAND, WA 99352
MIAMI, FL 33138
 
R

Ron Rosenfeld

Hi Ron I ran the code on this data and nothing was changed... No results so
thought I would show the exact data and see what I was missing...I have may
have mislead as to the data having two commas, only 1. Please advise...
Regards patt

E F G
JACKSONVILLE, FL 32224
CHARLESTON, SC 29405
HIALEAH, FL 33016
OCALA, FL 34478-3200
RICHLAND, WA 99352
MIAMI, FL 33138
Click to expand...

Well, you wrote that the contents of your cell was "city,state,zip" That's two
commas and no spaces. Your example shows 1 comma and several spaces.

So I would do it differently, and, like in your previous problem, it's easy to
use regular expressions for this kind of problem. Again, you'll need to
properly set up the range. I hard coded it to G1:G100, but there are a variety
of ways to do this, depending on your requirements.

Note there are two lines turning off and then on "screenupdating". Once you
are satisfied that the macro is working correctly, "uncommenting" these lines
so they are active will enable to the macro to run more quickly.

================================================
Option Explicit
Sub ParseCityStateZip()
Dim c As Range, rgToParse As Range
Dim re As Object, mc As Object
Dim i As Long

Set rgToParse = [G1:G100]
'Application.ScreenUpdating = False

Set re = CreateObject("vbscript.regexp")
re.Pattern = "^([^,]+)\W*(\S+)\s+(\S+)$"

For Each c In rgToParse
With c
If re.test(.Text) = True Then
Set mc = re.Execute(.Text)
For i = 0 To 2
.Offset(0, i - 2).NumberFormat = "@"
.Offset(0, i - 2).Value = mc(0).submatches(i)
Next i
End If
End With
Next c
'Application.ScreenUpdating = True
End Sub
==========================================
--ron
 
P

Paul Mathews

Hi Pat, sorry about the delay in this response but I signed off last night
before you sent it. The compile problem you're encountering is being caused
by the automatic wrapping of the code by this response window. When you copy
and paste that wrapped code into your Excel VBA editor, it causes a compile
error because you must explicitly identify wrapped code (in the editor) by
entering "_ " at the end of each wrapped code line. So, the code should look
like what you see below (you can copy and paste the code below straight into
the VBA editor). Incidentally, Ron's code is far more elegant and
professional so if you're looking to learn VBA, be sure to take a close look
at what he wrote. Mine is just quick and dirty but will get you what you
need.

Sub ParseAddress()

'Parse comma-delimited city,state,zip data
Do While ActiveCell.Value <> ""
'City
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value, _
Application.WorksheetFunction.Find(",", ActiveCell.Value) - 1)
'State
ActiveCell.Offset(0, -1).Value = VBA.Mid(ActiveCell.Value, _
Application.WorksheetFunction.Find(",", ActiveCell.Value) + 1, 2)
'Zip Code
ActiveCell.Value = "'" & VBA.Right(ActiveCell.Value, 5)
'Select next data record
ActiveCell.Offset(1, 0).Select
Loop


End Sub
 
P

pattlee

Thanks a million! Sorry about the original data set up. This worked
perfectly! Think I can learn from this last problem. the VBA code is getting
easier to understand. Much obliged, Ron

Ron Rosenfeld said:
Hi Ron I ran the code on this data and nothing was changed... No results so
thought I would show the exact data and see what I was missing...I have may
have mislead as to the data having two commas, only 1. Please advise...
Regards patt

E F G
JACKSONVILLE, FL 32224
CHARLESTON, SC 29405
HIALEAH, FL 33016
OCALA, FL 34478-3200
RICHLAND, WA 99352
MIAMI, FL 33138
Click to expand...

Well, you wrote that the contents of your cell was "city,state,zip" That's two
commas and no spaces. Your example shows 1 comma and several spaces.

So I would do it differently, and, like in your previous problem, it's easy to
use regular expressions for this kind of problem. Again, you'll need to
properly set up the range. I hard coded it to G1:G100, but there are a variety
of ways to do this, depending on your requirements.

Note there are two lines turning off and then on "screenupdating". Once you
are satisfied that the macro is working correctly, "uncommenting" these lines
so they are active will enable to the macro to run more quickly.

================================================
Option Explicit
Sub ParseCityStateZip()
Dim c As Range, rgToParse As Range
Dim re As Object, mc As Object
Dim i As Long

Set rgToParse = [G1:G100]
'Application.ScreenUpdating = False

Set re = CreateObject("vbscript.regexp")
re.Pattern = "^([^,]+)\W*(\S+)\s+(\S+)$"

For Each c In rgToParse
With c
If re.test(.Text) = True Then
Set mc = re.Execute(.Text)
For i = 0 To 2
.Offset(0, i - 2).NumberFormat = "@"
.Offset(0, i - 2).Value = mc(0).submatches(i)
Next i
End If
End With
Next c
'Application.ScreenUpdating = True
End Sub
==========================================
--ron
Click to expand...
 
P

pattlee

Thanks Paul, This code also gave me the results I needed. I am so obliged to
you for your help.. have sent both versions of code to others who are
involved with this data. We are very appreciative. (and eager to learn)
Regards Patt
 
R

Ron Rosenfeld

Thanks a million! Sorry about the original data set up. This worked
perfectly! Think I can learn from this last problem. the VBA code is getting
easier to understand. Much obliged, Ron
Click to expand...

You're welcome. Glad to help. No apologies necessary. Thanks for the
feedback.

--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address Format changed after Migration 0
Move rows into columns 3
Text and Numeric lookup 5
Offset function with inconsisent data location 1
How do I make a vertical report export into a vertical report? 3
VLOOKUP Addresses for Invoice 1
parseing out City, State ?? 3
change row to column 2

Top