Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
On Wed, 21 May 2008 17:44:01 -0700, pattlee
wrote: 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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
Hi Ron read your message but fail to see where the code is .. Am I missing
something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "pattlee" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
On Wed, 21 May 2008 18:36:21 -0700, pattlee
wrote: Hi Ron read your message but fail to see where the code is .. Am I missing something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "pattlee" wrote: 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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
Thanks ron will try and let you know Regards patt
"Ron Rosenfeld" wrote: On Wed, 21 May 2008 18:36:21 -0700, pattlee wrote: Hi Ron read your message but fail to see where the code is .. Am I missing something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "Paul Mathews" wrote: 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 "pattlee" wrote: 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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
"pattlee" wrote: Thanks ron will try and let you know Regards patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 18:36:21 -0700, pattlee wrote: Hi Ron read your message but fail to see where the code is .. Am I missing something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 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 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 City STATE ZIP JACKSONVILLE, FL 32224 CHARLESTON, SC 29405 HIALEAH, FL 33016 OCALA, FL 34478-3200 RICHLAND, WA 99352 MIAMI, FL 33138 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "Paul Mathews" wrote: 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 "pattlee" wrote: 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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
On Wed, 21 May 2008 21:05:00 -0700, pattlee
wrote: "pattlee" wrote: Thanks ron will try and let you know Regards patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 18:36:21 -0700, pattlee wrote: Hi Ron read your message but fail to see where the code is .. Am I missing something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 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 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 City STATE ZIP JACKSONVILLE, FL 32224 CHARLESTON, SC 29405 HIALEAH, FL 33016 OCALA, FL 34478-3200 RICHLAND, WA 99352 MIAMI, FL 33138 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "pattlee" wrote: 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 "Paul Mathews" wrote: 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 "pattlee" wrote: 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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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" wrote: On Wed, 21 May 2008 21:05:00 -0700, pattlee wrote: "pattlee" wrote: Thanks ron will try and let you know Regards patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 18:36:21 -0700, pattlee wrote: Hi Ron read your message but fail to see where the code is .. Am I missing something? Patt "Ron Rosenfeld" wrote: On Wed, 21 May 2008 17:44:01 -0700, pattlee wrote: 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 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 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 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 City STATE ZIP JACKSONVILLE, FL 32224 CHARLESTON, SC 29405 HIALEAH, FL 33016 OCALA, FL 34478-3200 RICHLAND, WA 99352 MIAMI, FL 33138 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
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 "Paul Mathews" wrote: 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 "pattlee" wrote: 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 "Paul Mathews" wrote: 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 "pattlee" wrote: 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. "Paul Mathews" wrote: 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 "pattlee" wrote: 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move text
On Thu, 22 May 2008 09:48:03 -0700, pattlee
wrote: 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 You're welcome. Glad to help. No apologies necessary. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move text | Excel Worksheet Functions | |||
Find text and move | Excel Discussion (Misc queries) | |||
find text and move it | Excel Discussion (Misc queries) | |||
move a text box to the back | Excel Discussion (Misc queries) | |||
How to move text from Row to Column | Excel Discussion (Misc queries) |