Convert scanned labels into excel format?

[Rosannafe]

I created labels from a very large excel database with the format name/company/address/city/state/zip/contact# I printed out the labels and sure enough the next day the hard drive failed. Now I need to find a way to convert the label format into the original format. When I scan the labels it creates cells exactly where each line ends..so when I send that to excel it looks like:
Name
Address
City State Zip
Name
Address
City State Zip
I have used the copy->transpose, but that is taking just as long as retyping. Any suggestions? I was thinking of having my cells like this:
A1 A2 A3 A4
A5 A6 A7 A8

but trying to copy this format doesn't stay, it just increments and becomes:
A1 A2 A3 A4
A2 A3 A4 A5

Thanks, hope that was kinda clear. Appreciate the help

 

[SkipVought]

Hi,

What happened to Company and Contact#?

And where does A8 come from -- I only count 7 in...

name/company/address/city/state/zip/contact#

Paste an example in here. Is the line format consistent?

If so, it's not a particularly difficult problem. But it looks as if some lines might have a single data element, while other lines (city/state/zip) have multiples.

A few examples will tell us if these multiples can be parsed among other things.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Rosannafe]

Thanks for replying. When I created the labels, the contact number was not on them even though they were in the excel file. I will have to type them manually. As for the divisions, it is as follows
Name
Company
Address
City State Zip
And this repeats as labels would. I am trying to get them to be:
Name Company Address City State Zip
Name Company Address etc.
as most excel sheets are formatted. It would take forever for me to cut, transpose and paste. I am wondering if there is an easier way without using vb code. I tried to have the cells repeat one under another like B1=A1(being the first name) then B2=A5(being the second name) etc, but it would only increment A1 A2 etc. Hope that clears my problem up. Thanks! (and dividng city state zip into 3 separate columns is easy, its just a matter of getting them horizontal instead of vertical).

 

[SkipVought]

I need to see several examples to especially see how city state and zip are delimited. There is no problem is the city state zip are consistantly formatted...

city name, ST 99999-9999

a coma delimiting city, 2-character state abbreviation and 5 to 10 character zip

Please confirm

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Rosannafe]

Yes, that is how city state and zip are in the field. The real problem is the order of these fields.
Name
Company
Address
city, state zip
Name
address
city state zip
over and over again like in labels, but this is not how a dtabase should look. Thanks again.

 

[SkipVought]

Are you also stating that Company is NOT included in every record?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Assuming that EACH RECORD CONSISTS OF 4 ROWS...

Here's how to format your sheet...

1. insert rows above your column of data so that your data starts in column A row 3

2. leave column B empty

3. in row 1 columns C thru F enter 0 thru 3

4. in C3 enter

=IF(MOD(ROW()-3,4)=0,$A3,C2)

5. in D3 enter

=IF(MOD(ROW()-3,4)=0,"",IF(MOD(ROW()-3,4)=D$1,$A3,D2))

6. copy D3 - paste in e3 & f3

7. copy C3:F3 - paste C4 thru last row of source data

8. select ALL cells c3:??? containing formulas

9. right click in selection and select copy

10. right click again and select Paste Special /Values/OK

Now, every row that is not empty in column F is a complete set of data -- sort accordingly.

One last step is to parse city,state,zip

select the column containing city,state,zip
use the Text to Columns Wizard (menu item Data/Text to Columns...) in 2 steps to parse...

first using the comma delimiter to separate city from state,zip

next select column containing state,zip using the space delimiter to separate state and zip

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Rosannafe]

I'll try this tommorow, thanks!