Bulk Add Apostrophes to Cells

Excel sometimes recognises things and sometimes it doesn't, for example if you've got a cell with a value that legitimately starts with a zero or an equals sign (membership numbers for example or passwords) then excel is going to start trying to be clever, either it will assume that you're entering a numeric value and strip off the leading zeros (rubbish for references) or it will start trying to evaluate a formula that won't make sense and will resolve to 0!.

To get around this excel realises that it's not going to get everything right and recognises that if a cell is prefixed with an apostrophe that everything after it should be interpreted verbatim and will pass up the content without the apostrophe when the cell's value is referenced.

Awesome!

 I thought so too right? the only problem is that sometimes this needs to be done to a lot of cells at once - fortunately here are two different nuggets of code to help out with this task, as usual, hit Alt+F11 to open the vba editor and paste these in:

Sub Addapostrophe()
    Dim Cell As Range
    For Each Cell In Selection
    Cell.Value = "'" & Cell.Value
    Next Cell
End Sub

The above allows you to select a range, go to the vba window, put your cursor into the vba method and hit F5 and every cell in your selection should be prefixed with the required apostrophe. If however it's easier for you to enter your range through code then the below has you covered.

Sub AddapostropheSpecific()
    Dim Cell As Range
    For Each Cell In ThisWorkbook.Worksheets("Sheet1").Range("n1:T6")
    Cell.Value = "'" & Cell.Value
    Next Cell
End Sub

These two are great for one hit wonders but if you've got a number of different ranges to affect then here's one final piece of code to use, this defines two subs and as soon as the ReplaceAll sub has been configured to call the operative sub in the way that you want you can just hit F5 within ReplaceAll and excel will run off and make all of the amendments that you've asked for - job done!.

Sub ReplaceAll()

PrefixRangeWithString "'", ThisWorkbook.Worksheets("Sheet1").Range("N1:T6")
PrefixRangeWithString "#", ThisWorkbook.Worksheets("Sheet2").Range("C5:C53")

End Sub


Sub PrefixRangeWithString(Prefix As String, Rng As Range)
    Dim Cell As Range
    For Each Cell In Rng
    Cell.Value = Prefix & Cell.Value
    Next Cell
End Sub

obviously these chunks of code have been set up to prefix any cell value but you can see how easily it could be amended to make all kinds of bulk changes to data within a workbook.