Excel Help

[Closed]
Report
-
 Jamie -
Hello,

I have approx 4000 entries in column A listed as follows

Robert & Sam Smith
12218 Country Hills Terrace
Glen Allen, VA 23059
Michael & Kristine
555-8048

Gary & Gayle Smith
218 E. Brook Run Drive
Richmond, VA 23238
555-1223

I need to move them to horizontal columns with the headers Member name, address, zipcode,children, and phone number.

Im not a computer whiz so strict techinal instructions will throw me way off. can anyone helppp?? its taking forever to copy and paste special.

3 replies

Posts
19532
Registration date
Wednesday October 8, 2008
Status
Contributor
Last seen
June 15, 2019
1,891
It CAN'T be done. Your are asking EXCEL to search through a string and determine where one field ends and another begins. Only a human or a $100,000 custom computer program can do this.

What was the original source for this data? If it was a comma delimited file exported from another computer/system you might be able to do what you want.
Paste the following into your vba code window:

Sub Format_Address()

On Error Resume Next
Application.ScreenUpdating = False
'Loop through each record changing from horizontal to vertical
For I = 1 To 4000
Application.Goto Reference:="R1C1"
If ActiveCell = "" Then GoTo 5 Else GoTo 10
5 Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
GoTo 20
10
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
20 Next I

'Delete Column and Sort in Ascending order
Range("A1").Select
Selection.EntireColumn.Delete
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub
Thanks you for the response David, I pasted it into the code window but what do I do next? it didnt change anything :(
ok david I figured it out and it worked perfectly except its only working for the first 16 rows ..... how do I change that???
Ok this time it sorted them all, but they are seperating all the information into too many columns, I only use columns A-F....is there a way to get around that?

Thankksss
Jamie
Thanks you for the response David, I pasted it into the code window but what do I do next? it didnt change anything