Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do i get rid of hidden apostrophes in cells?
How do I get rid of all the hidden apostrophes poplating every cell with
data, text and numeric? |
#2
|
|||
|
|||
Try Tools Edit Replace ' Replace With leave bank Replace All
Regards, "brokenmonkey" wrote in message ... How do I get rid of all the hidden apostrophes poplating every cell with data, text and numeric? |
#3
|
|||
|
|||
When I tried REplace to try and answer this question, Excel didn't find the
Lead '. If you are having the same issue, brokenmonkey, you might try: One way to fix a column like this: Create an extra column. Put this formula in it: =LEFT(A1,LEN(A1)) Copy it down. Then Copy all the cells and Edit | Paste Special, Values. Last Delete the original column. tj "Alan" wrote: Try Tools Edit Replace ' Replace With leave bank Replace All Regards, "brokenmonkey" wrote in message ... How do I get rid of all the hidden apostrophes poplating every cell with data, text and numeric? |
#4
|
|||
|
|||
OR
Just select the column with the leading, hidden apostrophes, and simply open and close "TextToColumns". Select the column. <Data <TextToColumns <Finish And you're done! -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "tjtjjtjt" wrote in message ... When I tried REplace to try and answer this question, Excel didn't find the Lead '. If you are having the same issue, brokenmonkey, you might try: One way to fix a column like this: Create an extra column. Put this formula in it: =LEFT(A1,LEN(A1)) Copy it down. Then Copy all the cells and Edit | Paste Special, Values. Last Delete the original column. tj "Alan" wrote: Try Tools Edit Replace ' Replace With leave bank Replace All Regards, "brokenmonkey" wrote in message ... How do I get rid of all the hidden apostrophes poplating every cell with data, text and numeric? |
#5
|
|||
|
|||
You must be a Monarch user.
If you use Excel 2k (like I do), try either one of these two macro's . They both work for me. I hear Excel 2002 has a tool to do this built in. Sub a_ApostroRemove1() ' ' 'Removes hidden apostrophes that are first characters. 'Works on cells with formulas, text, or values. For Each currentCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants) If currentCell.HasFormula = False Then 'Verifies that procedure does not change the 'cell with the active formula so that it contains 'only the value. currentCell.Formula = currentCell.Value End If Next End Sub Sub a_ApostroRemove2() ' ' 'Removes hidden apostrophes that are first characters. 'Public Sub ApostroRemove() Dim currentCell As Range On Error Resume Next For Each currentCell In Selection.SpecialCells( _ xlCellTypeConstants, xlTextValues) With currentCell .Formula = .Value End With Next On Error GoTo 0 End Sub "brokenmonkey" wrote: How do I get rid of all the hidden apostrophes poplating every cell with data, text and numeric? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Chart disappears when source data cells are hidden | Excel Discussion (Misc queries) | |||
Calculating without including Hidden Cells | Excel Discussion (Misc queries) |