Excel....query

[mixodorian]

Could anyone help me out, I am trying to convert excel column, into a
database (as shown below)

i.e...
A
Substance Misuse Service
Telephone: 01466 794433
7 Castle Street, Huntly, Huntly, AB54 8BP
Open: Monday - Friday: 9am - 5pm
(2 ROW GAP)

Substance Misuse Service
Telephone: 01467 620660
8 North Street, Inverurie, AB51 4QR
Open: Monday - Friday: 9am - 5pm
(2 ROW GAP...THOUSANDS OF RECORDS ETC)

I need to fill column B with the name of the organisation, Column C
with the telephone number Column D with the address (more on which in a
minute, and column E with the Details......

i.e.....

B ------------- C -------- D-------- C(Sorry coudn't line up
Column Headings)
Substance Misuse Service Telephone: 01466 794433
Address Details
Substance Misuse Service Telephone: 01467 620660
Address Details

Is there any way I can do this - without individually copying and
pasting each record over into they're respective columns?

Also, in the address field....
ie
8 North Street, Inverurie, AB51 4QR

I also need to split this information into seperate columns...... are
there any functions or formulas that would enable me to do this?

Thanks to everybody for their kind and generous advice in advance...

 

[Roger Govier]

Hi

First, make a COPY of your data (just in case!!)

In cell B1 enter =A2, in C1 enter =A3, in D1 enter =A4
Mark cells B26, then use the fill handle at the bottom right corner of
cell D6 to drag down through your page, until you see 0 appearing in the row.

Now, Mark the whole range of data, Copy, move cursor to cell A1 and Paste
Special>Values to fix the data.

Next, mark the whole range and sort by Column B (telephone) and this will
sort all of the "dross" to the bottom of the list so you can delete all of
these rows.

Finally, mark columns D:G, right click>Insert to create 4 blank columns.
Mark the range of data in column C >Data>Text to
columns>Delimited>Next>select Comma delimiter>Finish.

Delete any empty columns, format column widths to suit, insert a header row
with Organisation, Telephone, Add1, Add2 etc. and Sort the whole range of
data as required.

If you want to get rid of the Telephone: throughout (which is superfluous)
type Ctrl+H to bring up Find and Replace. In the Find pane type Telephone:,
leave the Replace pane Blank and select Replace all.



Regards

Roger Govier

 

[Roger Govier]

Hi

To be clear about the find and replace part
Find Telephone:
Replace

The comma I put in my sentence could have been misinterpreted as part of the
instruction.

Regards

Roger Govier

 

[mixodorian]

Thanks for that reply.... I will try it asap....

 

[mixodorian]

Thanks for that reply.... I will try it asap....

 

[Bob Phillips]

Here is some code
Sub Test()
Dim plastron As Long
Dim i As Long
Dim iRow As Long
Dim tmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iRow = 1
For i = 1 To iLastRow Step 6
Cells(iRow, "B").Value = Cells(i, "A").Value
Cells(iRow, "C").Value = Application.Substitute( _
Cells(i + 1, "A").Value, "Telephone: ", "")
tmp = Split(Cells(i + 2, "A").Value, ",")
Cells(iRow, "D").Resize(, UBound(tmp) + 1) = tmp
Cells(iRow, "I").Value = Application.Substitute( _
Cells(i + 3, "A").Value, "Open: ", "")
iRow = iRow + 1
Next i

Rows(iRow).Resize(iLastRow - iRow + 1).Delete

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[mixodorian]

It nearly worked (it got about 130 records), but I think the informatio
I want to move around is a little bit to imprecise for it (an irregula
number of comma's or delimiters in the address cell - between 2 an
5.) and this throws the information around into other cells..

Is there anyway of adjusting it and ignoring the problem of splittlin
the address cell for now

 

[Bob Phillips]

Sub Test()
Dim plastron As Long
Dim i As Long
Dim iRow As Long
Dim tmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iRow = 1
For i = 1 To iLastRow Step 6
Cells(iRow, "B").Value = Cells(i, "A").Value
Cells(iRow, "C").Value = Application.Substitute( _
Cells(i + 1, "A").Value, "Telephone: ", "")
Cells(iRow, "D").Value = Cells(i+2,"A").Value
Cells(iRow, "E").Value = Application.Substitute( _
Cells(i + 3, "A").Value, "Open: ", "")
iRow = iRow + 1
Next i

Rows(iRow).Resize(iLastRow - iRow + 1).Delete

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[mixodorian]

Played about the info a bit (inconsistant gaps were causing mayhem) and
got your code to work, learned a great deal doing it as well (by playing
around a bit with the code).

Brilliant result, this will make my project go a lot easier.

Loads of thanks.