TEXT TO COLUMNS

[Chris]

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

 

[Frank Kabel]

Hi
do you have an indicator which divides separate data sets (e.g. a blank
line between each of your records)

 

[chris huber]

Frank-- The data is divided by commas.

Thanks

 

[Frank Kabel]

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)

 

[chris huber]

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!

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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

 

[Mike Fogleman]

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

 

[duane]

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

 

[David McRitchie]

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

 

[chris huber]

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!

 

[chris huber]

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

 

[David McRitchie]

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/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.

 

[Mike Fogleman]

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

 

[David McRitchie]

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