How to distinguish between the same characters?

[z3phyr84]

I've attempted this all day today and have been unable to get anywhere, except through a long, tedius macro that does one excel box at a time. Here's the situation and what I need to do:

 

I have data that gets exported into a table format, which I copy and paste into Excel. The data can ONLY be exported in the following example format:

 

:06:08

:07:08

1:06:35

:06:30

 

Each of these are representing minutes and seconds related to my project. Now, in Excel, the formulas don't work properly unless a '0' is entered before the first semi-colon, like this:

 

0:06:08

0:07:08

1:06:35

0:06:30

 

I need to have it highlight and copy the table (yeah, easy) and then have it add a 0 in front of any semi colon that does *not* have a digit preceding it.

 

Eg: Add 0 in front of :06:08 but not in front of 1:06:35

 

I can't simply replace every case of ':' with '0:' because that would make it look like: 0:060:06, and it ruins a lot of my formulas. And, I can't figure out how to get it to recognize that 0:, 1:, 2:, 3:, 4:, 5:, 6:, 7:, 8:, and 9: are different than just : with nothing in preceding it.

 

Any possible solutions or direction would be much appreciated!

[Cory]

Once you have the exported data why don't you add the zeros before importation?

 

If it were me I would process that file or set of data and if it starts with a colon add a zero to the front of it.

 

Text File Begin Process: "list.txt"
 Variable Modify String: Copy Part of %T1% to %T2%
 If Variable %T2% = ":"
Variable Set String %T1% "0%T1%"
 End If
 Variable Modify String: Append %T1% to Text File
Text File End Process

 

<BTFBEG:001:000001:000000:c:\list.txt><REM2:
><TMVAR2:10:02:01:001:001:><REM2:
><IFVAR2:1:02:1::><REM2:
><TVAR2:01:01:0%T1%><REM2:
><ENDIF><REM2:
><TMVAR2:20:01:00:000:000:c:\listfixed.txtT><REM2:
><BTFEND>

[James]

Have you tried replacing " :" with 0:? In other words, insert a space before the colon.