Excel 2007 Replace and single quotes

[DWalker]

I have a spreadsheet that got loaded in Excel 2007 so the cells show the
following info, complete with single quotes:

'02' 'test' 'another column'
'07' 'stuff' 'more data'


I want to remove all of the single quotes but keep the leading zeros.
(I could do this with formulas, but there are 20 columns and over 32,000
rows. Replace seems like the way to go.)

So I tried to replace ' with (nothing).

1) If I do this on the whole workbook, and don't set the formats (which
are not set in the original workbook), Excel will dutifully remove the
single quotes, but also lose the leading zeros from all cells that have
strings that could be interpreted as numbers. (This example is a
simplification; there are really lots of columns, over 32000 rows, and
many columns have leading zeros that I need to keep).

2) As a test, to make sure I really knew how to use Replace with Excel
2007, I decided to highlight the first 2 rows, then set the "format" on
the "replaced" cells to "text" (to preserve the leading zeros), and did
the replace.

Oddly, Excel will then say "Excel has completed its search and made 12
replacements". But, NONE of the single quotes are gone! They are all
still there! I can do the same replace over and over, and each time,
Excel will tell me that it made 12 replacements. I don't believe it has
made ANY replacements, since the single quotes are still there.

The only way I can get the single quotes to really disappear is to
select "clear replace format" from the "replace" formatting box. Of
course, then, after the replacement, I lose the leading zeros, and
everything is stored as a number.

3) When I picked "Text" for the replacement format, the word "Preview*"
appears in the box to the left of "Format". The Help gives me no clue
what "Preview*" means.

Questions: How do I remove these single quotes and keep the zeros?

And what does "Preview*" mean?

Thanks.

David Walker

 

[Bob Flanagan]

Just a thought: change all single quotes to "zzzzz". Then change the
format to text and change the "zzzzz" to nothing.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

You could use the macro below to remove leading and trailing single quotes
from data.

In case you are not familiar with macros, here are some instructions to
insert the macro and run it.

NOTE: Make a backup copy of your workbook in case the macro does not do what
you expect.

Open the workbook where you want the single quotes removed.

Ensure that you have permitted macros to run. Click on the Microsoft Office
button (Large button top left of screen)

Select Excel Options (Towards bottom right of dialogue box)

Select Trust Centre-> Trust Centre Settings-> Macro Settings-> Set ‘Disable
all macros with notification’ and check the box ‘Trust access to the VBA
project object model’ and then close all of the options the dialogue boxes.

Alt/F11 will open the macro editor (VBA Editor).

Select menu item Insert. Then select Module. A large white area appears on
the right of the screen.

Select and copy the macro below. (Ensure you copy from the line:-

Sub Replace_Single_Quotes() to End Sub)

Paste the macro into the VBA Editor.

Edit the following line near the top of the macro and insert your worksheet
name between the dounble quotes:-

Worksheets("Sheet1").Select

The lines which appear in green in the VBA editor are comments just to let
you know what the macro is doing. I have tested for the leading and trailing
single quotes so that you do not have characters deleted if there just
happens to be some data which does not have the quotes.


Change windows back to the Excel workbook. (Alt/F11 toggles between
Worksheet and VBA Editor)

Press Alt/F8 to view the macro dialogue box. Select the macro and then
select Run.

Note that it is essential to format the cells as text.

To remove the macro from the workbook:-

Alt/F11 to bring up the VBA editor.

Right click on Module1 in the left side pane.
Select ‘Remove Module1’
Click on ‘No’ in the warning dialogue box.
Click on the X in top right corner to close the VBA editor.


Sub Replace_Single_Quotes()
Dim rng1 As Range
Dim c As Range

Worksheets("Sheet1").Select

'Apply variable to the used range
Set rng1 = ActiveSheet.UsedRange

'Format the used range as text
rng1.NumberFormat = "@"

'The following processes each cell in the used range
For Each c In rng1

'Test that cell is not empty
If Len(Trim(c)) > 0 Then

'Test if leading single quote exists
If Left(c, 1) = Chr(39) Then
'Remove leading single quote
c = Mid(c, 2) 'Replace from 2nd character
End If

'Test if trailing single quote exists
If Right(c, 1) = Chr(39) Then
'Remove trailing single quote
c = Left(c, Len(c) - 1)
End If
End If
Next c

End Sub


Regards,

OssieMac

 

[Guest]

Just an additional note. Where I said 'Note that it is essential to format
the cells as text.' You do not have to do that. The macro handles it. I just
wanted you to know what is occurring.

Regards,

OssieMac

 

[DWalker]

Just an additional note. Where I said 'Note that it is essential to
format the cells as text.' You do not have to do that. The macro
handles it. I just wanted you to know what is occurring.

Regards,

OssieMac

Thanks, I happen to be very familiar with macro programming in Excel, and I
could have written such a macro myself. I appreciate your example.

My issues were that Replace should be able to do the task, and most
important, Replace shouldn't tell me "12 changes made" and yet not really
change anything, and if I do the replace over and over, it will tell me
"12 changes made" over and over, with no changes made.

Isn't that a bug?

David Walker

 

[DWalker]

Just a thought: change all single quotes to "zzzzz". Then change the
format to text and change the "zzzzz" to nothing.

Bob Flanagan
Macro Systems
http://www.add-ins.com

That's an interesting approach, and it will probably work. It just seems
kind of klugey.

The fact that Excel tells me "12 changes made" and yet it doesn't change
anything, and if I do the change over and over, it will tell me "12 changes
made" over and over, and yet not change anything... this has to be a bug in
Excel 2007.

David