Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hello -
I have a column of imported data in Excel which groups together the following fields: First Name Last Name Addr1 Addr2 Addr3 City ST Zip Country All items EXCEPT for State and Zip are comma separated. My question is this: how do I convert the Text to Columns knowing that all the data is not the same i.e. some records have Addr1,Addr2; others have only Addr1; otehrs have all three. When I change the text to columns, I need the data to line up correctly. Once I get that figured out, how would I separate the Zip and ST data? Thank you in advance for your help! Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi
do you have an indicator which divides separate data sets (e.g. a blank line between each of your records) -- Regards Frank Kabel Frankfurt, Germany "Chris" schrieb im Newsbeitrag om... Hello - I have a column of imported data in Excel which groups together the following fields: First Name Last Name Addr1 Addr2 Addr3 City ST Zip Country All items EXCEPT for State and Zip are comma separated. My question is this: how do I convert the Text to Columns knowing that all the data is not the same i.e. some records have Addr1,Addr2; others have only Addr1; otehrs have all three. When I change the text to columns, I need the data to line up correctly. Once I get that figured out, how would I separate the Zip and ST data? Thank you in advance for your help! Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Frank-- The data is divided by commas. Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi
not sure about your layout. Your example shows ONE column of data. Could you post a real example of your data (plain text please) -- Regards Frank Kabel Frankfurt, Germany "chris huber" schrieb im Newsbeitrag ... Frank-- The data is divided by commas. Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Name, Addr1, Addr2, Addr3, City, State Zip, Country
Please note, there is no comma between State and Zip. In any case, that is the layout. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi
this is a little bit confusing now. Are these data elements in one cell, in one row, in one column. Please post some REAL-Life data together with cell references :-) -- Regards Frank Kabel Frankfurt, Germany "chris huber" schrieb im Newsbeitrag ... Name, Addr1, Addr2, Addr3, City, State Zip, Country Please note, there is no comma between State and Zip. In any case, that is the layout. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
A2:Name1, Addr1, Addr2, Addr3, City, State Zip, Country
A3:Name2, Addr1, Addr2, City, State Zip, Country A4:Name3, Addr1, City, State Zip, Country A5:Name4, Addr1, Addr2, Addr3, City, State Zip, Country Would be my take. He wants each broken into 8 columns Name | addr1 | addr2 | addr3 | city | state | zip | country -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi this is a little bit confusing now. Are these data elements in one cell, in one row, in one column. Please post some REAL-Life data together with cell references :-) -- Regards Frank Kabel Frankfurt, Germany "chris huber" schrieb im Newsbeitrag ... Name, Addr1, Addr2, Addr3, City, State Zip, Country Please note, there is no comma between State and Zip. In any case, that is the layout. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
I understand that all the fields of data are in one cell as comma and space
delimited. The only difference between rows of data are the presence of 1, 2, or 3 address fields. If a line of data does not have 2 or 3 address fields, does it have empty placeholders for the field? ie. Name, Addr1,,, City, State Zip, Country If placeholders for empty fields are not present, then Text to Columns will put City in Addr2 column, etc.in the above example. You would need to process your data with a formula that would count commas and if there are less that 6, then concantenate the proper number of place holders(",") before the City field. The only flaw in this would be if the data had Addr1 & Addr3, but not Addr2. That would put Addr3 in the Addr2 column and a blank cell in the Addr3 column. Close, but no cookie! That is a close as I can get you to setting up your data for a Text to Columns. Not knowing how to differentiate Addr2 from Addr3 data when there are 5 commas is the stickler. If you can see a way to tell the difference, then use it also in the formula. But without seeing the actual data, I cannot. Mike F "Frank Kabel" wrote in message ... Hi this is a little bit confusing now. Are these data elements in one cell, in one row, in one column. Please post some REAL-Life data together with cell references :-) -- Regards Frank Kabel Frankfurt, Germany "chris huber" schrieb im Newsbeitrag ... Name, Addr1, Addr2, Addr3, City, State Zip, Country Please note, there is no comma between State and Zip. In any case, that is the layout. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
I would guess that the only addr combinations would be:
addr1 addr1, addr2 addr1, addr2, addr3 given this (guess) running text to columns followed by a routine t count the number of data columns in each row and move the City, Stat Zip, Country to the right if there are less than 8 columns of data -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi Chris,
You do mean they go like this, don't you FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, County Okay I did see you response so I would suggest a macro to count the commas and if less than 7 insert the correct number of missing commas before the 2nd to last comma. After that use Text to commas with comma as the delimiter. FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, County FirstName, Last Name,,,, City, ST Zip, County FirstName, Last Name, Addr1,,, City, ST Zip, County FirstName, Last Name, Addr1, Addr2,, City, ST Zip, County The following macro would slip in the extra columns as you see them above and then do the Text to Columns. Sub PopulateAddr3Data() 'D.McRitchie, programming, 2004-08-29 'Data with less than seven commas need more commas Dim Cell As Range, CCnt As Long, i As Long, j As Long, insert As Long Dim Rng As Range Set Rng = Intersect(Selection, Columns("A:A"), ActiveSheet.UsedRange) If Rng Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each Cell In Intersect(Rng, Rng) CCnt = Len(Cell) - Len(Application.Substitute(Cell, ",", "")) If CCnt < 7 And CCnt 0 Then insert = 7 - CCnt 'Insert = Left(",,,,,,,", 7 - CCnt) i = 0: j = 0 While j < (CCnt - 2) i = i + 1 If Mid(Cell.Value, i, 1) = "," Then j = j + 1 Wend Cell.Value = Left(Cell.Value, i) & _ Left(",,,,,,,", insert) & Mid(Cell.Value, i + 1) End If Next Cell 'Text to Columns.... Rng.TextToColumns Destination:=ActiveCell, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _ Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris" wrote in message om... Hello - I have a column of imported data in Excel which groups together the following fields: First Name Last Name Addr1 Addr2 Addr3 City ST Zip Country All items EXCEPT for State and Zip are comma separated. My question is this: how do I convert the Text to Columns knowing that all the data is not the same i.e. some records have Addr1,Addr2; others have only Addr1; otehrs have all three. When I change the text to columns, I need the data to line up correctly. Once I get that figured out, how would I separate the Zip and ST data? Thank you in advance for your help! Chris |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Counting commas huh? I never would have considered that. You guys are all great ... thanks for all the help. Mike, you hit it on the head as far as what my issue was. The addresses were landing in the wrong fields. David, I am going to give the macro a shot this evening when I can get out of the grasp of the Monday Workday Blues. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
David,
I can't get the macro to work. I am reading, testing, learning ... but not successful, yet. Here is what my data actually looks like. There are 3 records below. Each record is located in one cell, i.e. A1 = first record, A2 = second record, etc. ALso, the name AND the St/zip do not have commas. Would you mind helping me put that macro into use? Thank you again! Chris John Olson, 17 Elm St, Manchester, MA 01944, United States Marlee Margulies, 400 Carona Place, Silver Spring, MD 20905, United States Sam Linsky, TNT, 3500 W. Olive Ave., 15th Floor, Burbank, CA 91505, United States *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi Chris,
The macro is an Event macro and is installed differently: Right click on the Sheet Tab, View Code, then insert code More on Event macros in http://www.mvps.org/dmcritchie/excel/event.htm Since first name and lastname are combined when starting, change the code In your example, you have an extra comma where TNT should not be in the address position, but be part of the name, You can "fix" that with a global change of ", TNT," "~ TNT," before converting, and converting the tilde back to comma at then end of the macro. Sam Linsky, TNT, 3500 W. Olive Ave., 15th Floor, Burbank, CA 91505, United States Since first name and last name are not separated by commas change the three 7's in the code lines between FOR ,,, NEXT from 7 to 6 [my solution was based on your original post] You can separate the names and the "ST zip" afterwards once they are in their own cells in the spreadsheet , see http://www.mvps.org/dmcritchie/excel...tm#seplastterm You will have to insert the columns, just like you would for text to columns, but I would suggest that the macro is more practical. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "chris huber" wrote ... I can't get the macro to work. I am reading, testing, learning ... but not successful, yet. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Dave didn't quite get Rng defined correctly so the macro would loop through
the data. Also inserted 1 too many commas. And now we need to take the macro a couple of steps further. Once we get the first round of commas in place for the missing Addr fields ( all rows should have 6 commas), then we need to find the first space (" "), which would be right after FirstName, and insert a comma before it so it would be FirstName, LastName. Now the row would have 7 commas. Now we would count over 6 commas and find the 2nd space ((" ") because there is a space before State) and insert a comma before it. Now we can run the Text to Columns routine. Here is David's macro modified to loop with 1 less comma. I added a line where the code needs to go for name and zip commas. Sorry I don't have enough time right now to continue this, maybe David can pick up on it or you can use what is here to finish it yourself. Good Luck...Mike F Sub PopulateAddr3Data() 'D.McRitchie, programming, 2004-08-29 'Data with less than Six commas need more commas Dim Cell As Range, CCnt As Long, i As Long, j As Long, insert As Long Dim Rng As Range Set Rng = ActiveSheet.UsedRange If Rng Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each Cell In Rng CCnt = Len(Cell) - Len(Application.Substitute(Cell, ",", "")) If CCnt < 6 And CCnt 0 Then insert = 6 - CCnt 'Insert = Left(",,,,,,,", 6 - CCnt) i = 0: j = 0 While j < (CCnt - 2) i = i + 1 If Mid(Cell.Value, i, 1) = "," Then j = j + 1 Wend Cell.Value = Left(Cell.Value, i) & _ Left(",,,,,,,", insert) & Mid(Cell.Value, i + 1) End If 'Here is where code is needed to separate names and then zip code. Next Cell 'Text to Columns.... Rng.TextToColumns Destination:=ActiveCell, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _ Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "chris huber" wrote in message ... David, I can't get the macro to work. I am reading, testing, learning ... but not successful, yet. Here is what my data actually looks like. There are 3 records below. Each record is located in one cell, i.e. A1 = first record, A2 = second record, etc. ALso, the name AND the St/zip do not have commas. Would you mind helping me put that macro into use? Thank you again! Chris John Olson, 17 Elm St, Manchester, MA 01944, United States Marlee Margulies, 400 Carona Place, Silver Spring, MD 20905, United States Sam Linsky, TNT, 3500 W. Olive Ave., 15th Floor, Burbank, CA 91505, United States *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
TEXT TO COLUMNS
Hi Mike and Chris,
I got Rng defined correctly, I just didn't tell you how to run macro. My macros normally require a selection before running, Makes macros much more flexible. First make a selection, any of these but not a single cell - entire contiguous rows - entire worksheet (you can use Ctrl+A) - entire column A - contiguous selection of cells involving cells in Column A Here is a modified version of the macro, to use Rng(1,1) instead of ActiveCell which could get you messed up and moving data to a different row. Suggest first installing TrimALL and SepLastTerm into your personal.xls from http://www.mvps.org/dmcritchie/excel/join.htm#trimall Sub PopulateAddr3Data() 'D.McRitchie, programming, 2004-08-29 'Data with less than six commas need more commas Dim Cell As Range, CCnt As Long, i As Long, j As Long, insert As Long Dim Rng As Range Set Rng = Intersect(Selection, Columns("A:A"), ActiveSheet.UsedRange) If Rng Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Selection.Replace What:=", TNT,", Replacement:="~ TNT", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False For Each Cell In Intersect(Rng, Rng) CCnt = Len(Cell) - Len(Application.Substitute(Cell, ",", "")) If CCnt < 6 And CCnt 0 Then insert = 6 - CCnt 'Insert = Left(",,,,,,,", 6 - CCnt) i = 0: j = 0 While j < (CCnt - 2) i = i + 1 If Mid(Cell.Value, i, 1) = "," Then j = j + 1 Wend Cell.Value = Left(Cell.Value, i) & _ Left(",,,,,,,", insert) & Mid(Cell.Value, i + 1) End If Next Cell 'Text to Columns.... Rng.TextToColumns Destination:=Rng(1, 1), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _ Array(5, 1), Array(6, 1), Array(7, 1)) Rng.Resize(, 7).Select '-- tilde is an escape character for itself, so has to be doubled Selection.Replace What:="~~ ", Replacement:=", ", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True '-- See http://www.mvps.org/dmcritchie/excel/join.htm#trimall '-- invoke installed TrimALL code Application.Run "'personal.xls'!Trimall" '-- manually insert column before Column F, then before col B '-- manually use SepLastTerm from join.htm on cells in A and in F End Sub Test Data used: at A14:A16 (United States shortened to USA for posting) John Olson, 17 Elm St, Manchester, MA 01944, USA Marlee Margulies, 400 Carona Place, Silver Spring, MD 20905, USA Sam Linsky, TNT, 3500 W. Olive Ave., 15th Floor, Burbank, CA 91505,USA --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Mike Fogleman" wrote ... Dave didn't quite get Rng defined correctly so the macro would loop through the data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |