Related:
- Free excel help forum
- Excel free download - Download - Spreadsheets
- Excel date format dd.mm.yyyy - Guide
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
3 responses
xpcman
Posts
19530
Registration date
Wednesday October 8, 2008
Status
Contributor
Last seen
June 15, 2019
1,826
Oct 17, 2008 at 12:50 PM
Oct 17, 2008 at 12:50 PM
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.
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
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
