Text to Column

[Guest]

I have a sheet imported from Access. The cell has the following data from the
import...

602 E Church DR
#
Sugar Land, TX 77478-2612

My problem is the '#' symbol brought from the database. If I try to take
that column and convert from 'Text to Column' it cuts out the City & State
information. How can I get rid of the second line that has the '#' in it to
have the third line of data in cell?

Thanks,

 

[Guest]

Did you try to us fixed width rather then delimited when using text to columns?

 

[Guest]

All the address have different legnths, some with Suit #'s and so forth.

 

[Guest]

How about using 'find and replace'? I just did a quick test and went:
- hi-light the whole column of data
- 'edit'....'replace' in the find part I entered # and in replace I just
entered a space.

this got ride of the symbol.

 

[Guest]

I would attack this at the source database level (MS-Access) vs. trying to
correct it in Excel. Export this as a .csv file and see if it leaves the
leading zeros in place. Then, import it into Excel. Or, if you are not doing
it already, use Data Query in Excel to extract the data from the Access
database.

P.