Prev: Having a macro/function available in new spreadsheets
Next: vlookup + if
From: Suz771 on 2 Apr 2010 10:33
I have a spreadsheet where all the fields (except those with numbers) have a
' before the data in the cell. The ' does not show up in the cell, but only
in the formula line. CTRL+H does not work, nor does it work using the ~ with
the ' (either before or after the ' character).

I need to remove this character in order for my formulas to work. The
spreadsheet has over 10K rows and 170 columns.

Thank you,
Susie
From: Don Guillett on 2 Apr 2010 10:53
1.. In an empty cell on the worksheet, type the value 1.
2.. Select the cell that contains the value 1, and click Copy on the Edit
menu.
3.. Select the cells that contain the text that you want to replace, and
then click Paste Special on the Edit menu.
4.. Under Paste, click Values. Under Operation, click Multiply, and then
click OK.

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Suz771" <Suz771(a)discussions.microsoft.com> wrote in message
news:61AEBADD-58F7-4D3A-B254-A9F586DC072F(a)microsoft.com...
>I have a spreadsheet where all the fields (except those with numbers) have
>a
> ' before the data in the cell. The ' does not show up in the cell, but
> only
> in the formula line. CTRL+H does not work, nor does it work using the ~
> with
> the ' (either before or after the ' character).
>
> I need to remove this character in order for my formulas to work. The
> spreadsheet has over 10K rows and 170 columns.
>
> Thank you,
> Susie

From: מיכאל (מיקי) אבידן on 2 Apr 2010 10:55
Did you try to press ONLY ONCE the key combination CTRL and ~ ?
[try to press and HOLD the CTRL key and then press on the ~ key [located at
the far left of the horizontal numeric keys].
Micky


"Suz771" wrote:

> I have a spreadsheet where all the fields (except those with numbers) have a
> ' before the data in the cell. The ' does not show up in the cell, but only
> in the formula line. CTRL+H does not work, nor does it work using the ~ with
> the ' (either before or after the ' character).
>
> I need to remove this character in order for my formulas to work. The
> spreadsheet has over 10K rows and 170 columns.
>
> Thank you,
> Susie
From: Gord Dibben on 2 Apr 2010 11:01
If you change the alignment to center or right do the ' change to ^ or "

Turn off Transition Options.

What type of formulas are you using that care about the ' in cells without
numbers?


Gord Dibben MS Excel MVP

On Fri, 2 Apr 2010 07:33:02 -0700, Suz771 <Suz771(a)discussions.microsoft.com>
wrote:

>I have a spreadsheet where all the fields (except those with numbers) have a
>' before the data in the cell. The ' does not show up in the cell, but only
>in the formula line. CTRL+H does not work, nor does it work using the ~ with
>the ' (either before or after the ' character).
>
>I need to remove this character in order for my formulas to work. The
>spreadsheet has over 10K rows and 170 columns.
>
>Thank you,
>Susie

From: ozgrid.com on 2 Apr 2010 22:33
Use this code;

Sub RemoveIt()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
.Value = .Value
End With
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Suz771" <Suz771(a)discussions.microsoft.com> wrote in message
news:61AEBADD-58F7-4D3A-B254-A9F586DC072F(a)microsoft.com...
>I have a spreadsheet where all the fields (except those with numbers) have
>a
> ' before the data in the cell. The ' does not show up in the cell, but
> only
> in the formula line. CTRL+H does not work, nor does it work using the ~
> with
> the ' (either before or after the ' character).
>
> I need to remove this character in order for my formulas to work. The
> spreadsheet has over 10K rows and 170 columns.
>
> Thank you,
> Susie

 | 
Pages: 1
Prev: Having a macro/function available in new spreadsheets
Next: vlookup + if