A question about Excel

astina

For my reporting, I have to link two spreadsheets using vlookup based on customer names. Somehow when it is downloaded from system, one of the spreadsheets has got part of the customer names in a quotation mark while the other sheet hasn’t. This makes the vlookup ineffective.

My question is: is there a quick way to remove the quotation mark as I have many line items and it is impractical to remove them manually.

Thanks for your help.

AS JJ:

Steps:
1. Open your excel file in MS Excel
2. Select File menu-->Save As
3. In the pop-up window, pls choose "CSV(MS-DOS) *.csv" format and hit on "Save"
4. Open the saved csv file in UltraEdit or Notepad
5. Open the replace dialog and input "?" in "Find What" field while leave blank in "Replace With" field, then click on "start" button.
6. Save your csv file and exit.
7. Change your XXX.csv to XXX.xls
8. Open your excel sheet again and it would be correct now

astina

Thanks a lot.

Tried, when I came to step 5 and tried to do this replacement, there is a message saying that “?” can’t be found

astina  在 2005-6-9 03:02 PM 发表:

Thanks a lot.

Tried, when I came to step 5 and tried to do this replacement, there is a message saying that “?” can’t be found

Can you let me know what the quote mark has been converted to in Step 5?
Can you see the "?" mark in your txt editor?

baolqun

the other way is using UltraEdit "column mode"
delete the marks easily if they are exactly in a column

If you cannot see any "?" in your UltraEdit, you may try to save your csv file generated at Step 3. Then, rename the file from XXX.csv to XXX.xls to see if those "?" marks have been removed or not.

astina

Thank you all very much. It is fixed finally. Special thanks to ThePlaceToBe who talked me through on phone.

ThePlaceToBe

You are always welcome.

安州人

连AS的问题都没有看明白。不过，要自动区配和找到对应数据，我一般是配合使用OFFSET和MATCH函数。

溪霓冬雪

是不是达到这个水平的才算advance呀？那偶要赶快改偶得简历了...

小昭

quotation mark 是不是用EDIT里的replace with就可以换掉啊？就用空格替换引号就好了。这样比较简单，替换出来的东西看上去是没问题，不过我不知道格式是不是不一样了，V-LOOKUP是不是又会出问题。

我也经常用V-LOOKUP，觉得用EXCEL的习惯是能让电脑做的就让电脑做，坚决不自己一个个敲进去....有时候费半天劲LOOKUP的结果其实自己手敲一会就做完了：）...所以我更喜欢做数据量大的表格，这样做出来比较有成就感，做reconciliation也比较好对...唉，数据处理工作者的悲哀啊....啥时候都不忘reconciliation...