how do I copy text to columns from one cell to another?

[Guest]

I have 400 names and addresses in column A and in rows 1-400. I need to
separate the name and address to create mailing labels in different columns.

Ex:

FirstName LastName Street City State Zipcode

I used Data, text-to-columns for the first row, but I would like to do that
for the other rows without have to do each row individually.

Is there any way to copy the format or anything else?

 

[Guest]

Stressed,

Select your column first, then do Text to Columns

HTH

 

[jim]

You should be able to highlight column A and repeat the Data > Text to
Columns action. This will apply the change to every row in that column.

 

[Guest]

Do you want the FirstName, LastName, Street, City, State, and Zipcode in
different collumns or the other way around?. It would help if you showed an
example of how your data is now and how you would like it to look. From what
I gather it seems like you have First and Last Name in one Row on A and the
Address in the next Row of column A. If that is the case =IF(MOD(ROW(),
2)=1,B11&" "&B12,"") Should put everything in the same Column. Adjust it
as you need of course. You'll have blank rows inbetween which you can use
the AutoFilter to get rid of. If I do not understand how your data is set up
please explain further and I might be able to help more.

 

[Guest]

its not lettin me do that, can u be more specific?

 

[Guest]

This is what it looks like now
AWAD, BLANCHE4601 KINGSWELL AVELOS ANGELESCA90027 (All in column A)

I would like it to look like this: All in different columns. I'm trying to
create a mailing list.
Arreola, Rosa 8839 8th St Bloomington CA 92316

 

[Guest]

Stressed,

You successfuly converted one row already, correct?

You should be able to select multiple rows and peform the same operation.
This will do what you did to row one, to each of the rows that are
highlighted.

Did you used "Fixed Width" or "Delimited" for your first row?

 

[Guest]

Alright. I made a little Macro that should be able to do what you want. The
code isn't pretty but it should get the job done. Please be sure to change
the three variables I've set at the begining of the code to match how your
spreadsheet is layed out. I do recomend that you create a backup copy of
your spreadsheet just incase.

If you do not know how to set up the macro: Have the spreadsheet you wish
to change active on the screen and press F11. Then in the VBA editor double
click the spreadsheet you have your addresses in. Then copy the code I have
below and paste it into the VBA editor. Change the three Variable to fit
your spreadsheet. Then back in the excel Spreadsheet press Alt + F8 and run
col_Split.

If you set everything up correctly It should add a new spreadsheet then pop
up with a propt asking you if the street address is correct. It will not
include the numbers before the address. If it is correct press OK and if it
is not press Cancel. You will have to do that for every entry but I figure
that is as easy as I could make it. Hopefully that helps you out.




Public Sub col_Split()
Dim i As Integer, start As Integer, LastRow As Integer
Dim MyLen As Integer, j As Integer, Breaker As Integer
Dim Arr As Variant
Dim s As String, s2 As String, Clm As String
Dim WS As String
Dim Rrng As Range, Wrng As Range

''' =================================================
''' Change these to fit your Spreadsheet
''' Keep quotes if present
''' =================================================
''' Which column the text you wish to change is in
Clm = "D"
''' Which row the text you want to change starts on
start = 4
''' What is the Spreadsheet name
WS = "Sheet4"
''' =================================================

''' Finds the range of Rows to work on
i = start
Do While Range(Clm & i + 1).Value <> ""
i = i + 1
Loop
LastRow = i
Set Rrng = Range(Clm & start & ":" & Clm & LastRow)

Worksheets.Add
ActiveSheet.Name = "Address Output"
Set Wrng = Worksheets("Address Output").Range("A1:G" & LastRow)

i = start
For Each Row In Rrng
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(1)
Call Split_letters_numbers(s, s2)
Wrng.Range("A" & i).Value = s
Wrng.Range("C" & i).Value = s2
Arr = Split(Row.Range("A1").Value, ",")
Wrng.Range("B" & i).Value = Arr(0)
Breaker = 1
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(2)
For j = 3 To UBound(Arr) - 1
answer = MsgBox(Row.Range("A1").Value & vbNewLine & vbNewLine & _
s & vbNewLine & vbNewLine & "Press Okay If this is the full Street
address" & _
vbNewLine & "Press Cancel if it is not. (Number address not
included)", vbOKCancel)
If answer = vbCancel Then
s = s & " " & Arr(j)
Else
Exit For
End If
Next j
Wrng.Range("D" & i).Value = s
Do While j < UBound(Arr)
Wrng.Range("E" & i).Value = Arr(j) & " "
j = j + 1
Loop
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(UBound(Arr))
Call Split_letters_numbers(s, s2)
Wrng.Range("G" & i).Value = s2
Wrng.Range("F" & i).Value = Right(s, 2)
MyLen = Len(s)
Wrng.Range("E" & i).Value = Wrng.Range("E" & i).Value & Left(s, MyLen - 2)


i = i + 1
Next Row



End Sub

Private Sub Split_letters_numbers(ByRef s As String, ByRef s2 As String)
Dim C As String
Dim MyLen As Integer, i As Integer

MyLen = Len(s)
For i = 1 To MyLen
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
s2 = Mid(s, i, MyLen)
s = Left(s, i - 1)
i = MyLen
End If
Next i

End Sub

 

[Guest]

Use this one. Some of the lines were too long and the automatic formatting
will screw it up.



Public Sub col_Split()
Dim i As Integer, start As Integer, LastRow As Integer
Dim MyLen As Integer, j As Integer, Breaker As Integer
Dim Arr As Variant
Dim s As String, s2 As String, Clm As String
Dim WS As String
Dim Rrng As Range, Wrng As Range

''' =================================================
''' Change these to fit your Spreadsheet
''' Keep quotes if present
''' =================================================
''' Which column the text you wish to change is in
Clm = "D"
''' Which row the text you want to change starts on
start = 4
''' What is the Spreadsheet name
WS = "Sheet4"
''' =================================================

''' Finds the range of Rows to work on
i = start
Do While Range(Clm & i + 1).Value <> ""
i = i + 1
Loop
LastRow = i
Set Rrng = Range(Clm & start & ":" & Clm & LastRow)

Worksheets.Add
ActiveSheet.Name = "Address Output"
Set Wrng = Worksheets("Address Output").Range("A1:G" & LastRow)

i = start
For Each Row In Rrng
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(1)
Call Split_letters_numbers(s, s2)
Wrng.Range("A" & i).Value = s
Wrng.Range("C" & i).Value = s2
Arr = Split(Row.Range("A1").Value, ",")
Wrng.Range("B" & i).Value = Arr(0)
Breaker = 1
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(2)
For j = 3 To UBound(Arr) - 1
answer = MsgBox(Row.Range("A1").Value & vbNewLine & vbNewLine & _
s & vbNewLine & vbNewLine & "Press Okay If this is the full " & _
"Street address" & vbNewLine & "Press Cancel if it is not. " & _
"(Number address not included)", vbOKCancel)
If answer = vbCancel Then
s = s & " " & Arr(j)
Else
Exit For
End If
Next j
Wrng.Range("D" & i).Value = s
Do While j < UBound(Arr)
Wrng.Range("E" & i).Value = Arr(j) & " "
j = j + 1
Loop
Arr = Split(Row.Range("A1").Value, " ")
s = Arr(UBound(Arr))
Call Split_letters_numbers(s, s2)
Wrng.Range("G" & i).Value = s2
Wrng.Range("F" & i).Value = Right(s, 2)
MyLen = Len(s)
Wrng.Range("E" & i).Value = Wrng.Range("E" & i).Value & Left(s, MyLen - 2)


i = i + 1
Next Row



End Sub

Private Sub Split_letters_numbers(ByRef s As String, ByRef s2 As String)
Dim C As String
Dim MyLen As Integer, i As Integer

MyLen = Len(s)
For i = 1 To MyLen
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
s2 = Mid(s, i, MyLen)
s = Left(s, i - 1)
i = MyLen
End If
Next i

End Sub