Home » Questions » Computers [ Ask a new question ]

I have a spreadsheet were lots of cells have the character ' in the first position of each value, how to remove it?

I have a spreadsheet were lots of cells have the character ' in the first position of each value, how to remove it?

Hi want to format all of the values in a column to be a phone number, but the first character in many cells is

Asked by: Guest | Views: 75
Total answers/comments: 2
Guest [Entry]

"EDIT: I just tested my suggestion below the line, and it does not work, but there's good news. Copying those cells with a leading single-quote does not cause the leading single-quote to be copied to the clipboard. However, that means all you have to do is:

Select All | Copy
Paste into a new document in a text editor
Select All | Copy
Paste back into a new sheet or document in OO-Calc.

Assuming that:

your data does NOT contain formulas that you need to preserve, and
you want to remove that leading single-quote from ALL cells,

my first attempt would involve something like this:

Insert a blank column in Column A (this is for the ""Find"" below, so we don't miss any leading single-quote values in the first column of data)
Select All | Copy
Open up Notepad++ (or your text editor of choice with extended search-and-replace capability)
Paste into a new document in Notepad++
Ctrl+H to Search-and-Replace
Check the option for Extended search/replace

Find: ""\t'"" (backslash followed by tee followed by single-quote, this means search for Tab followed by single-quote)
Replace: ""\t"" (Tab)

Replace All
Select All in the document | Copy to clipboard
Paste into a new sheet or OO-calc document (when prompted, select the option to use Tab as the separator)"
Guest [Entry]

"I was able to reproduce your problem. The workaround I came up with is a little less elegant than I'd like it to be, but the following steps might work for you.

Insert a new column next to the one you are having a problem with.
Copy the column with the values that start with '
Select the new column you created in step 1.
Choose Paste Special and select Values.

The values will paste in the new column without the ' and you can then delete the original column.

I couldn't get it to work by pasting Values into the original column which is why I included the step of pasting them into another column.

There are a few scenarios where this might not work. If you have other formatting that you need, or if you have formulas in that column as well, this would break those things."