How can I make a macro in Excel to delete the first digit of a series of cells?

Question

I have a report where the formatting of a series of cells is wrong and I can't sum them because it thinks my numbers are letters. It's done as 00:00:00 but when I change the formatting from general to hh:mm:ss it stays as general. If I take the first 0 off I can change the formatting no problem and it will add them up. I tried to make a macro to take it off but when I try to run it with different number it always brings back the numbers from the first series I ran it on.

Answer 1

You don't need a macro to do this in Excel. Here's a cell formula that you can use and it works on both strings and numbers (assume the data you want to modify is in cell A1):

=VALUE(MID(A1,2,LEN(A1)))

The MID() function returns a substring of A1 by starting at the 2nd character and returning the rest of the characters. LEN() tells you how many characters are in A1. The VALUE() function converts the remaining substring to a number value (you will get an error if the remaining characters do not constitute a valid number).

Check the help to see the syntax for the functions.

Answer 2

If you have the flexibility to add a new column, you can use this formula to pull off the first character of a set of data with variable length contents:
=RIGHT(,(LEN()-1))

I believe this will convert the result to text...dug a bit and suggest that this might do it for you:

=TEXT(
TIMEVALUE(
RIGHT(,(LEN()-1))
),"hh:mm:ss")

In general I think you might be able to get what you need using the TIMEVALUE function...

Answer 3

This is very much version dependent.
You can do this easily with a Macro;
Check out mrexcel.com and search their archive, or ask a question. The people are great, and you can get quick results and great help