I'm in need of some help to create Excel Speadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

guruuno - 24 Jul 2006 02:50 GMT

I'm hoping someone can point me into the proper direction:

I have a customer who has been using the LABELS template in MS Word to
keep a list of customers.

The list is over 3,000 Names and addresses.

I've taken the list, brought it into Excel, but it's not in the correct
format.
(meaning, no 'headers', or 'fields' for the data. It's a 'flat file'.)

I do not want to recreate/re-enter all the data.

I'd like to have a database, sortable, searchable, etc., in Excel.

I did the many years ago, but it's well past the 15 years I did it, I
must be getting old! :)

The data is like this:

Mr. & Mrs. Customer
123 Easy Street
Anytown, Any State 12345

There are 3,000 of these entries.

I'd like to get the NAME, ADDRESS, CITY, STATE, ZIP into Excel/CSV,
etc.

Any way possible?

Thanks in advance

Reply to this Message

The Horny Goat - 24 Jul 2006 05:21 GMT

>I'm hoping someone can point me into the proper direction:
>
[quoted text clipped - 24 lines]
>I'd like to get the NAME, ADDRESS, CITY, STATE, ZIP into Excel/CSV,
>etc.

How is it delimited? If tab delimited you've got it very easy - just
import it as tab delimited etc. into Excel.

Separating State and Zip is going to be easy *IF* you're using the
standard two digit abbreviations and 5 digit zips throughout. Just use
=left$(a1,2) and =right$(a1,5). It'll be more difficult if some Zips
are 5+4 but still doable. If you're not using the standard two letter
state abbreviations you're in trouble. Similarly if you've got
Canadians on there, Canadian zip codes are not 5 digits but 6
alphabetic characters with a space between position 3 and 4.

So definitely do-able but not completely non-trivial.

If you want to reply with 10-20 names I could take a look - or you
could e-mail me via the true e-mail address shown for me at
http://www.chess.ca/governors.htm - I think you'll have no difficulty
determining which of these is me.

(Sorry - I do indeed use a fake address on Usenet to avoid spammers -
but deliberately keep it obvious enough that I am readily found in
most search engines)

Reply to this Message

guruuno - 25 Jul 2006 01:00 GMT

This is what I have from the WORD Document, and need to create either
Excel or Access
database with NAME, ADDRESS, CITY, STATE, ZIP fields.

ALL Zips are STANDARD 5 DIGIT with NJ before
(NO Canadian)

As far as I can tell, it's NOT delimited at all, hence the issue.

Thanks!
============================================
Mr. & Mrs. Abar <-------SEPARATE LINE (NEEDS TO BE IN A
COLUMN/FIELD)
134 Mye Avenue <-------SEPARATE LINE (NEEDS TO BE IN A COLUMN/FIELD)
Mill, NJ 07041 <-------SEPARATE LINE (NEEDS TO BE IN A COLUMN/FIELD)
L

Mr. & Mrs. Abenstein
11 Brown Drive
Living, NJ 07039
L

Mr. & Mrs. Abraham
12 Lel Avenue
Living, NJ 07039
L

Mr. & Mrs. Adam
30 Ride Road
Green, NJ 08812
W

Dr. Alexesco
443 Westfield Avenue
Roselle, NJ 07204
U

Reply to this Message

Gord Dibben - 25 Jul 2006 01:26 GMT

If all the data is as your example, i.e. 4 rows and a blank row, the code
below will move each to 4 columns per row.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 5 to account for the blank rows.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben Excel MVP

>This is what I have from the WORD Document, and need to create either
>Excel or Access
[quoted text clipped - 32 lines]
>Roselle, NJ 07204
>U

Gord Dibben MS Excel MVP

Reply to this Message

guruuno - 25 Jul 2006 03:29 GMT

Thank you, and I will attempt this on a copy of the data.

However, there are some records in this list with more than the 3
lines, as an example, it may state on line 2, (ATTN: JOE BLACK)

In this scenerio, how would I attemt to inclued extra data?

Thanks, I'll let you know how this works as is 1st...

Guruuno

Reply to this Message

guruuno - 25 Jul 2006 04:19 GMT

OK, ran this, with the exception of the 10 records with 5 lined of data
(I removed for this test), all was fine with an exception:

The field that contains the City + State + Zip is all inclusive, and
I'd like to be able to breakdown that data to incorporate CITY as a
seperate fiels as well as STATE and also ZIP.

So, instead of having City+State+Zip in one field, I'd like City, Stae,
Zip independent of each other.

Thanks....I'm making progress

Reply to this Message

Gord Dibben - 25 Jul 2006 04:49 GMT

Insert two columns right of Column C.

Select Column C and Data>Text to Columns>De-limited by >Comma>Finish.

Select Column E and Data.T to T>Fixed Width>Finish

This assumes all your City, State Zip cells are similarly formatted.

Gord

>OK, ran this, with the exception of the 10 records with 5 lined of data
>(I removed for this test), all was fine with an exception:
[quoted text clipped - 7 lines]
>
>Thanks....I'm making progress

Reply to this Message

guruuno - 25 Jul 2006 06:08 GMT

Everything workded out 100% perfectly, thanks for all the help, I now
am very happy, and educated.

Reply to this Message

Rate this thread: