Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading single quote (') from cells with Search/Replace
I have put together a sheet full of formulas. When creating the formulas I
put a single quote in front of all the equal signs (ie. '=A1+A2) so that I could see the formulas. My strategy was to do a Search/Replace to make the conversion to formulas. Search for: '= Replace with: = To my chagrin, the Search cannot find the single quote mark since it does not show up in the cell. Please help!! My sheet has over 500 lines. I can't go back through te cells one-by-one. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading single quote (') from cells with Search/Replace
One way is to use a macro:
Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection.Cells, _ Selection.Cells.SpecialCells(xlCellTypeConstants)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please select a range containing some text values" Exit Sub End If For Each myCell In myRng.Cells With myCell If Left(.Value, 1) = "=" Then .NumberFormat = "General" .Value = .Value End If End With Next myCell End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm I use $$$$$= to prefix my formulas sometimes. But you can also enter the formulas as formulas and use Tools|Options|view tab|check formulas (ctrl-` control-backquote (to the left of the 1/! on my USA keyboard) also toggles that formula view.) Arun wrote: I have put together a sheet full of formulas. When creating the formulas I put a single quote in front of all the equal signs (ie. '=A1+A2) so that I could see the formulas. My strategy was to do a Search/Replace to make the conversion to formulas. Search for: '= Replace with: = To my chagrin, the Search cannot find the single quote mark since it does not show up in the cell. Please help!! My sheet has over 500 lines. I can't go back through te cells one-by-one. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading single quote (') from cells with Search/Replace
Try this:
Select a column range containing the impacted cells (Like A1:A100) DataText-to-ColumnsClick the [Finish] button Repeat for other columns. Does that knock out all of the leading apostrophes? *********** Regards, Ron XL2002, WinXP-Pro "Arun" wrote: I have put together a sheet full of formulas. When creating the formulas I put a single quote in front of all the equal signs (ie. '=A1+A2) so that I could see the formulas. My strategy was to do a Search/Replace to make the conversion to formulas. Search for: '= Replace with: = To my chagrin, the Search cannot find the single quote mark since it does not show up in the cell. Please help!! My sheet has over 500 lines. I can't go back through te cells one-by-one. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading single quote (') from cells with Search/Replace
Highlight the entire column.
Go to Data, text to columns, choose Delimited. Check the Other Box and place the ' in the box. Thant will take care of your problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should merge cells without removing content of any cell | Excel Discussion (Misc queries) | |||
Is it possible to show leading zeros for "number" cells? | Excel Discussion (Misc queries) | |||
Removing single quote from an excel sheet | Excel Discussion (Misc queries) | |||
How to change a single cell's width | Excel Worksheet Functions | |||
single quote 10-digit number that has leading zeroes & then conca. | Excel Worksheet Functions |