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
--
Regards
Frank Kabel
Frankfurt, Germany
"Chris" <chu...@yahoo.com> schrieb im Newsbeitrag
news:602acdb2.04082...@posting.google.com...
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
--
Regards
Frank Kabel
Frankfurt, Germany
"chris huber" <chu...@yahoo.com> schrieb im Newsbeitrag
news:uYxK5Maj...@TK2MSFTNGP11.phx.gbl...
Please note, there is no comma between State and Zip.
In any case, that is the layout.
Thanks!
--
Regards
Frank Kabel
Frankfurt, Germany
"chris huber" <chu...@yahoo.com> schrieb im Newsbeitrag
news:euOw2Ebj...@TK2MSFTNGP15.phx.gbl...
Would be my take. He wants each broken into 8 columns
Name | addr1 | addr2 | addr3 | city | state | zip | country
--
Regards,
Tom Ogilvy
"Frank Kabel" <frank...@freenet.de> wrote in message
news:Ob3nbebj...@TK2MSFTNGP10.phx.gbl...
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" <frank...@freenet.de> wrote in message
news:Ob3nbebj...@TK2MSFTNGP10.phx.gbl...
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" <chu...@yahoo.com> wrote in message news:602acdb2.04082...@posting.google.com...
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.
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
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/join.htm#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" <chu...@yahoo.com> wrote ...
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" <chu...@yahoo.com> wrote in message
news:%232AH9zl...@tk2msftngp13.phx.gbl...
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" <mikefo...@insightbb.com> wrote ...