Edit many cells at once to add a charater to existing data?

  • Thread starter Thread starter ABTurner
  • Start date Start date
A

ABTurner

I have a column of data and I need to add an apostrophe to the beginning of
all the cells without changing the remaining content. How can I do this? I
know how to do this for individual cells, but I would like to do it to all
cells at once.
 
The easiest way is to write a formula in a new column. then copy the results
from the new column back to the old column and use PastSpecial with values
selected. the formula to use in the new column would be this.

="'" & A1 This is a double quote, then an apostrophe, and then another
double quote


Copy the formula down the column.
 
Select the cells and run this small macro:

Sub DontQuoteMe()
Dim q As String
q = Chr(39)
For Each r In Selection
r.Value = q & q & r.Value
Next
End Sub

Note you need to add the single quote twice to get it to appear in the cell
as well as the formula bar.
 
Helper column ="'"&A1 and copy down. The copy this column, & Edit/ Paste
Special/ Values over the original data and delete the helper column.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding an apostrophe to a column of numbers 6
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
How do I Add Data at the beginning of multiple cells in Excel? 3
change cell link for many check boxes at once 5
Stepping in to a cell 3
Excel Add ^ to a row of cells containing letters 3
COPYING CONDITIONAL FORMATTING WHEN COPYING CELLS OVER 2
Removal of cell objects 5

Back
Top