Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
one way?
Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
Can you elaborate? What does Len do? What does Right do?
Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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 wrote in message oups.com... Can you elaborate? What does Len do? What does Right do? Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
Hi
in the VBA editor select these functions and press F1 -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag oups.com... Can you elaborate? What does Len do? What does Right do? Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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. Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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 wrote in message oups.com... 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. Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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] Michael Malinsky wrote: 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 wrote in message oups.com... 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. Don Guillett wrote: one way? Sub nomoreapos() For Each c In Selection c.Value = Right(c, Len(c) - 1) Next c -- Don Guillett SalesAid Software wrote in message oups.com... 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
If (starts with ', then delete the ')
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/ _______ wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel E-2007 starts, but Installer also starts 3 times??? | Setting up and Configuration of Excel | |||
Excel Starts When I hit te Zero Key | Excel Discussion (Misc queries) | |||
Zero starts of a number | Excel Worksheet Functions | |||
Delete row if cell starts with ........... | New Users to Excel | |||
formula starts : =+ what does plus indicate? | Excel Worksheet Functions |