Convert From Word To Excel

[James SB]

Can this be done and if so, here is my question
The word dowload customer base reads as follows:
Lowes Of Alabaster, AL #2525
235 Colonial Drive
Alabaster, Al 35007
(205)685-4343
FAX: (205) 685 5434
Can Iconvert to excel and if so Can I seperate the first line from the store
name state and store number(also rmove the # from the number)
Seperate city state zip on second line and remove Fax from fax number.

Using Windows XP media 2003.

 

[Joel]

the answer is yes provided all the entries have the same 5 rows of data. the
problem occur if diffferent entries have different numbe of entries. if one
of the telephone number is missing how can you tell if the fax or regular
number is missing. What if a store has two address lines. A store may be in
a mall and has the street address and the mall name.

By the way, where do you want the reults to go.

 

[James SB]

In some cases there might be a few locations that might not have a number.
Would that throw the whole thing out of whack or is it as simple as
physically correcting the few new locations that lack phone or fax numbers. I
am not sure what you mean by where do I want the results to go. If possible
in excel reading across as opposed to reading down as it is in word. Sorry ,I
forgot to state that in first post.

 

[James SB]

I don't follow what you mean about where do I want the results to go. If
possible from a word docment to an excel document. Reading across as opposed
to reading down. I forgot to mention that in first post. Some of the accounts
might not have phone or fax yet-would that through the whole thing out of
whack or could I just document the ones I have to fix.

 

[James SB]

I went through and they are 5 lines for each one.

LOWE'S OF ALABASTER, AL, #2525
235 COLONIAL PROMENADE PARKWAY
ALABASTER, AL 35007
(205) 685-4060
FAX: (205) 685-4063
.. Word reads down, I need to read across in excel if possible

 

[Joel]

the macro will move the data from sheet1 to sheet2. Modify as required.

Sub ConvertData()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

DestRow = 1
SourceRow = 1
With SourceSht
Do While .Range("A" & SourceRow) <> ""
FirstLine = Split(.Range("A" & SourceRow), ",")
StoreName = Trim(FirstLine(0))
StoreState = Trim(FirstLine(1))
StoreNum = Mid(Trim(FirstLine(2)), 2)

StoreAddress = .Range("A" & (SourceRow + 1))

StoreCityZip = Split(.Range("A" & (SourceRow + 2)), ",")
StoreCity = Trim(StoreCityZip(0))
StoreStateZip = Split(Trim(StoreCityZip(1)), " ")
StoreZip = Trim(StoreStateZip(1))

StorePhone = Trim(.Range("A" & (SourceRow + 3)))

StoreFax = Trim(.Range("A" & (SourceRow + 4)))
StoreFax = Trim(Replace(StoreFax, "FAX:", ""))

With DestSht
.Range("A" & DestRow) = StoreName
.Range("B" & DestRow) = StoreState
.Range("C" & DestRow) = StoreNum
.Range("D" & DestRow) = StoreAddress
.Range("E" & DestRow) = StoreCity
.Range("F" & DestRow) = StoreZip
.Range("G" & DestRow) = StorePhone
.Range("H" & DestRow) = StoreFax
End With

SourceRow = SourceRow + 5
DestRow = DestRow + 1

Loop
End With

Columns("A:H").AutoFit

End Sub