If (starts with ', then delete the ')

[michael_mcclellan]

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Don Guillett]

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

 

[michael_mcclellan]

Can you elaborate? What does Len do? What does Right do?

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Michael Malinsky]

The line

c.Value=(Right(c,Len(c)-1)

Can be interpreted as follows:

First we determine the value in cell c, for example '12/31/04
Then working inside out in the parenthesis, we take the length (Len) of the
value in the cell (in this case, 9) then subtract 1 (result = 8).
We next extract from the value of cell c, 8 characters from right to left,
resulting in 12/31/04 (without the ').
We then replace the value of cell c ('12/31/04) with those 8 characters
(12/31/04).

Len determines the length of the value and Right pulls "x" number of
characters going from left to right as in
Right(string, 1)
returns the rightmost charcter.

There are more elaborate explanations in the Excel VBA help.

HTH

Can you elaborate? What does Len do? What does Right do?

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Frank Kabel]

Hi
in the VBA editor select these functions and press F1

--
Regards
Frank Kabel
Frankfurt, Germany

Can you elaborate? What does Len do? What does Right do?

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[michael_mcclellan]

This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Michael Malinsky]

Apparently replacing the cell value with itself elimiates the apostrophe:

Sub nomoreapos()

For Each c In Selection
c.Value = c.Value
Next c

End Sub

This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Jake Marx]

Hi Michael McClellan,

One thing to be aware of - replacing all cells with their values will remove
any formulas you have. So you may want to do something like this:

Sub FixDates()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If Not c.HasFormula And IsDate(c.Value) Then _
c.Value = c.Value
Next c
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Apparently replacing the cell value with itself elimiates the
apostrophe:

Sub nomoreapos()

For Each c In Selection
c.Value = c.Value
Next c

End Sub

This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with
a "'" in front of all of them, and excel will not recognize them
as dates. I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Don Guillett]

You're right. Use the .value method

--
Don Guillett
SalesAid Software
(e-mail address removed)

This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

 

[Jon Peltier]

Easier than mucking about in VBA:

Copy a blank cell
Select the cells which have an apostrophe as the first character (which is not
recognized by VBA or Excel)
Edit menu > Paste Special > Values and Operation - Add options

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______