Not a clue

  • Thread starter Thread starter antony
  • Start date Start date
A

antony

Guys

more help needed

i have a column of data that needs splitting, but the text to column
cannot perform the task i need.

the spreadsheet i have been given is basically a downloded address lis
from a website. the problem is that the "address4" column contain
address4 information and the next columns information. the next colom
is the county information and i need to separate them. to make matter
worse some of the county information is the only information i
"address4" and this is the reason I cannot use text to columns. what
do have is a spreadsheet of the 60+ counties of the UK and i wa
thinking that perhaps Excel could sort and move the counties using th
information on that sheet. Unfortunatley I have run out of the require
talent to perform this task. the sheet contains 6000 addresses- se
sample of the sheet attached.

Thanx in advance to anyone who can hel

Attachment filename: example to forum.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47074
 
If text to columns won't do it, give examples of text contained in one cell
that you would like split up, and how it should look once it's split into 2+
cells.

E.g.
I now have;
1234 Main St. Glastenberry Havenshire 95682-01929

I want;
1234 Main St. |Glastenberry Havenshire|95682|01929
 
Hi Anthony,
These are text newsgroups where you are expected to describe your
problem in words. Technically you have not attached a file because
Exceltips has placed it on their severvs, but I doubt that it will be around
in two weeks or twenty years. But I expect Google Groups or a successor
to retain postings that long. The archives can only be searched as text
so everything should be in text so that people can search and get answers
essentially benefiting from your question. So I will phrase your question
so that it can be seen without having to open a file.

The address lines can consist of columns C through F and the
city should be in column G and the county in column H. But city
and county are appearing immediately after the last actual address line.

C2: address line 1
D2; address line 2
E2: city (no more address lines this should be empty)
F2: County (no more address lines this should be empty)
G2: (city belongs here)
H2: (county belongs here)

Sub RealignAddressColumns()
Dim rng As Range, cell As Range
ActiveSheet.Copy after:=ActiveSheet
Set rng = Intersect(Range("H:H"), ActiveSheet.UsedRange)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cell In rng
If cell.Value = "" Then
If cell.Offset(0, -3).Value = "" Then
cell.Value = cell.Offset(0, -4)
cell.Offset(0, -4).Clear
cell.Offset(0, -1).Value = cell.Offset(0, -5).Value
cell.Offset(0, -5).Clear
ElseIf cell.Offset(0, -2).Value = "" Then
cell.Value = cell.Offset(0, -3)
cell.Offset(0, -3).Clear
cell.Offset(0, -1).Value = cell.Offset(0, -4).Value
cell.Offset(0, -4).Clear
ElseIf cell.Offset(0, -1).Value = "" Then
cell.Value = cell.Offset(0, -2)
cell.Offset(0, -2).Clear
cell.Offset(0, -1).Value = cell.Offset(0, -3).Value
cell.Offset(0, -3).Clear
ElseIf cell.Offset(0, 0).Value = "" Then
cell.Value = cell.Offset(0, -1)
cell.Offset(0, -1).Value = cell.Offset(0, -2).Value
cell.Offset(0, -2).Clear
End If
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Next cell
End Sub
 
Antony
Your example shows counties in both the Address3 and Address4 columns,
but you mention doing something with only the Address4 columns. Do you want
to separate the counties from the Address3 column also?
Your problem will take VBA to solve. If you wish, send me a sample file
with about 1000 addresses. I already have the sample file with the counties
listed. Is that list complete? I'll write you some code to do what you
need.
Answer the above questions and send me the file. Remove "cobia97" from
my email address. HTH Otto
 
otto

I will send you the info but your email address does not appear in you
mail to me!

regards

Anton
 
Antony
If you attempt to reply direct to me from within the newsgroup, you
should see my email address. It is (e-mail address removed). Remove
"cobia97". Otto
 
Back
Top