Text to Column
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, |
Text to Column
Did you try to us fixed width rather then delimited when using text to columns?
"JLD" wrote: 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, |
Text to Column
All the address have different legnths, some with Suit #'s and so forth.
"tim m" wrote: Did you try to us fixed width rather then delimited when using text to columns? "JLD" wrote: 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, |
Text to Column
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. "JLD" wrote: All the address have different legnths, some with Suit #'s and so forth. "tim m" wrote: Did you try to us fixed width rather then delimited when using text to columns? "JLD" wrote: 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, |
Text to Column
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. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "tim m" wrote: 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. "JLD" wrote: All the address have different legnths, some with Suit #'s and so forth. "tim m" wrote: Did you try to us fixed width rather then delimited when using text to columns? "JLD" wrote: 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, |
All times are GMT +1. The time now is 01:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com