Find/Replace a single quote

Posted by Dave Stimpson on September 28, 1999 2:24 PM

I've been passed a spreadsheet which contains numbers - however some of the numbers have a '(single quote) prefix which turns them into text - however the way the data has been created these are not obvious i.e. they are not left justified. I'd like to use a Find / Replace function to get rid of the single quotes, bur Excel cannt find the value ' (unlike WORD where you can find special characters). Is there a quicker way to to this rather than the goto -> special -> constant text commands?

Posted by Myra on September 28, 1999 6:52 PM

Rather than doing a find/replace you could "convert text to columns":

Select the column you wish to convert and choose "Text to Columns" from the "Data" pull down menu. The "wizard" will guide you through the steps to convert your column. In step 3 of the wizard, select "general" as the column format and the preceding single quotes will be removed.

Posted by Dave Stimpson on September 28, 1999 11:06 PM

Thanks, but the data contains SUM formulas throughout (Obvioulsy the totals are incorrect coz of the text values. The only way I've been able to correct this so far is by replicating the data at the end of the row with =VALUE(Cell). and copying, paste values back over the original data - but there are so many blocks of data it takes more time than its worth.

Any othre suggestions welcome!

Posted by Ivan Moala on September 29, 1999 12:23 AM

Yes, you say that the only way you have been
able to do it is by the use of Value(Cell).
Then try the following macro;

Sub CleanTextData()
Dim Textcells As Range
Dim Row As Integer
Dim cell As Range

On Error Resume Next
Set Textcells = Range("A1").SpecialCells(xlCellTypeConstants, 2)

If Textcells Is Nothing Then
MsgBox "No Text"
Exit Sub
End If

For Each cell In Textcells
If Asc(Left(cell, 1)) = Chr(34) Then
cell.Value = Val(cell.Text)
End If
Next cell
End Sub

This will clean up the cells with 'before the
number = chr(34).
Note: excel will still evaluate your formulas
with the text values = '54 so your sums should
still be OK.

regards

Ivan

Posted by Chris on September 29, 1999 8:57 AM

You can also just change the text to values within your formulas. This way you can leave the ' in front of the numbers. For Example, if you wanted to do a sum formula, you could use the following:

=SUM(VALUE(A1:A100))

This must be entered as an array formula. Therefore, enter the formula using Ctrl+Shft+Enter rather than just Enter.

Just another option.

Chris