How Can I Manipulate Numbers in Excel's General Number Format?

[Jobrag]

I have an csv file that has a column of numbers in the form ab:cd:ef:ghy The cell's number format is general, I want to be able (if other criteria are met) to change y into y+1. The final column must again have the form ab:cd:ef:ghy and the number format general, can anyone out there give me an idea how to manipulate numbers in this format.

 

[I like Serena]

What about "=MID(A1,12,1)"?
This picks out "y",
Then you can add one, perhaps after using "=N(value)".
Finally you can concatenate everything with "=LEFT(A1,11)&C1".

 

[Jobrag]

I like Serena
Spot on, thank you, you have just out geeked our office resident geek who will now have to buy much beer, thanks again.

 

[I like Serena]

I have already geeked out my own office resident geeks.
I'm only glad that other people are willing to buy my beer!

Say, did you already vote?
https://www.physicsforums.com/forumdisplay.php?f=213

 

[LudusRex]

It is possible to manipulate numbers in this format using the Excel General number format. One approach would be to use the SUBSTITUTE function to replace the last digit (y) with a new value (y+1). This can be done by first converting the numbers in the column to text using the TEXT function, then using the SUBSTITUTE function to replace the last character with the new value. Finally, the numbers can be converted back to the original format using the TEXT function again. This process can be automated using a combination of formulas and conditional formatting. Alternatively, a macro can be written to perform this task.