add an apostrophe at the beginning of the cell

R

ramyadixit99

New Member
Joined
Jan 2, 2015
Messages
9
Hi,

I have a sheet which has =========== in some of the cells.
Macro i need to write should do the following
Check each cell in the sheet
If it begins with =, it needs to add an ' (apostrophe) at the beginning.

So that excel doesnt treat the = as a formula.

Regards,
Ramya
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this

Code: 
Sub ap()
Dim lRow As Long
Dim Rng As Range, aCells As Range
With ActiveSheet
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set Rng = .Range("A2:A" & lRow)
        For Each aCells In Rng
            If Left(aCells, 1) = "=" Then aCells.Value = "'" & aCells.Value
        Next aCells
End With
End Sub

It looks at column A from A2 to the last row with data in.
 
Upvote 0
Hey,

This worked fine for the column A. But how do i extend to all the rows and columns that has data in it.

Regards,
Ramya
 
Upvote 0
Hi,

Try this.. You need to select your entire data before running this macro.

Code: 
Sub addanapostrophe()
Dim i As Long
For Each cell In Selection
    If Not cell.HasFormula Then
    ' Do nothing
    Else: cell.Value = "'"  &  cell.Value
    End If
Next cell
    
End Sub

Regards,
Zaska
 
Last edited:
Upvote 0
and what if you use the find replace function built in excel?

you can even record a makro select all cells and do the find-replace which will give you a code
 
Last edited:
Upvote 0
zaska said:
Hi,

Try this.. You need to select your entire data before running this macro.

Code: 
Sub addanapostrophe()
Dim i As Long
For Each cell In Selection
    If Not cell.HasFormula Then
    ' Do nothing
    Else: cell.Value = "'" & cell.Value
    End If
Next cell
    
End Sub

Regards,
Zaska
Click to expand...
be careful he is not looking for formulas in the range he is looking for cells which starts with several equal signs
 
Upvote 0
hippiehacker said:
be careful he is not looking for formulas in the range he is looking for cells which starts with several equal signs
Click to expand...


Sorry If i have mistaken, as far as i know it is not possible to have several equal signs , excel will popup typo error unless it is preceded with an apostrophe.
 
Upvote 0
Yes Zaska , you are right. Excel will not allow you to have several equal to sign. But the data that i have has been exported to excel and hence doesn't give me the error unless i try to find and replace something.
 
Last edited:
Upvote 0
Hippiehacker,
I am not able to record the macro, because i wanna specify that only if the cell starts with = add a ' before it.
 
Upvote 0
ramyadixit99 said:
Yes Zaska , you are right. Excel will not allow you to have several equal to sign. But the data that i have has been exported to excel and hence doesn't give me the error unless i try to find and replace something.
Click to expand...

Is the above macro working for you?
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
How to keep the leading apostrophe from a cell when storing into a string variable?
Replies
2
Views
241
wiseone
W
A
  • Question
Adjusting data in cell
2
Replies
13
Views
327
Joe4
Joe4
F
  • Question
Hiding the leading apostrophe
Replies
9
Views
1K
shinigamilight
S
S
  • Solved
Multiple embedded IF statements with VLOOKUPs not working
Replies
2
Views
323
shawnrpg
S
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
173
Ramballah
Ramballah

Forum statistics

Threads
1,214,415
Messages
6,119,381
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top