I have a set of data name address and phone..... that I imported into Excel using OCR software from a printed list and the best I could get the information into excel was all in one cell so the cell reads (Jane Smith ####### 1234 main street ##### 444/555-9876 ## Your Town, US 55889 ### Fax 555/784-9999 ## jsmith@smith.com) # = spaces. I have been able to break out the name and the phone and fax using Formulas like FIND, LEFT, MID But I am not sure how I extract the address and the email because there is not pattern. The data wizard is not working either because the spaces are not consistant between the informatin in all cells. Any suggestions ?
2007-02-22 06:32:03 · 5 answers · asked by anneonceagain 2 in Computers & Internet ➔ Software
I know you mention the spaces are not consistent -- so are you also saying the number of characters assigned to each field are also inconsistent? Are you actually seeing pound signs or is that just part of your example.
Not actually seeing your file it's difficult to say -- but I have two options that might work for you.
1. If the number of characters assigned to each field is consistent. Save the file with a "txt" extension with "Save As". Close the file and reopen it in Excel. This will bring up the "Text Import Wizard" -- choose "Fixed Width" then "Next" -- in the bottom part of Step 2 -- just click to insert lines that break it into columns. Then hit finished -- there's no need for step 3.
2. If you actually have pound signs it will be really easy. First to a find and replace (Ctrl+H) and replace "##" with "#" and keep repeating until there are no more "##" to be found. Now save as txt -- close and reopen -- choose "Delimited" go to step 2 and for delimiter choose "other" and insert "#" -- click finish.
If neither of those examples works feel free to send me a message and I'll give you my e-mail address and you can send me a better example.
Good Luck!
2007-02-22 06:48:38 · answer #1 · answered by thatgirl 6 · 0⤊ 0⤋
Go back a step to the imported OCR file which I assume was just ASCII. Place commas between each data field in the file and
Then import your file as a .csv (comma separated) into Excel and see if that helps.
Good Luck
2007-02-22 06:41:59 · answer #2 · answered by snvffy 7 · 0⤊ 0⤋
Easy...
1) highlight the column which has your original data...
2) In drop-down menus click data....text-to-columns
3) Once the menu is open select "delimited" click next
4) change teh default delimiter from "tab" to other and put a '#' character in the space provided.
5) click finish and delete the extra blank columns it will also insert because of the extra #'s
2007-02-22 06:42:29 · answer #3 · answered by Pawn 2 · 0⤊ 0⤋
Are your columns wide enough? Sometimes I get that when the info in the cell is too wide to be displayed in the regular column width.
2007-02-22 06:41:35 · answer #4 · answered by aku_aku78 2 · 0⤊ 0⤋
widen your columns, if the data is too large to fit excel replaces with #########
if not that then do the find "#" and replace with " " (a blank space) it will take out all the number signs
2007-02-22 06:39:41 · answer #5 · answered by csucdartgirl 7 · 0⤊ 0⤋