Need to remove leading single quote from text cells

**katcar** · 10-09-2009, 03:54 PM

I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")

Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here")
(note leading single quote).

I cannot use the replace function to edit them out, so must hand edit each one out - tedious at best.

Does anyone know how to remove these. The formulas don't work otherwise.
Thank you in advance for any help you can offer.

**GuruWannaB** · 10-09-2009, 03:57 PM

Not sure why you can't use a find/replace...just do a find for '=hyperlink and replaced it with =hyperlink

**JP Romano** · 10-09-2009, 04:02 PM

I'm not able to replicate your problem either. I did a replace of the apostrophe with (nothing) and it worked fine. Maybe if you attach one of your spreadsheets here we can help.

**shg** · 10-09-2009, 04:02 PM

Select the column, do data > text to columns, finish

**katcar** · 10-09-2009, 04:48 PM

The Data-> Text to Columns-> Finish worked!!!

I won't bother to post the worksheet, but doing find/replace with the single quote did not work. Excel did not find the single quote as it is not seen as part of the contents of the cell.

Thank you SO very much!