Time exported w/ space/comma, Convert to Number
 

Hello,

I have a sample Data below...
A = original exported data, it has 1 space in front... for thousand, also
with space in front and with comma.
B = is my first step
E = result I need

Everything worked fine except when the Orig Data goes 10,000:00 and above,
just like the last data where I have 16,940:43; 6940:43; 6940.72...the first
digit is omitted, this goes with any data above 10,000:00.

Thank you in advance :)

A B C D E
Orig Data Remove Check for Convert Result Needed
Space Comma to Thousnd (Number Format)

6:30 6:30:00 F F 6.50
31:00 31:00:00 F F 31.00
975:00 975:00:00 F F 975.00
1,167:30 F 3 1167:30 1167.50
160:00 160:00:00 F F 160.00
31:00 31:00:00 F F 31.00
1,167:30 F 3 1167:30 1167.50
6,268:00 F 3 6268:00 6268.00
306:00 306:00:00 F F 306.00
96:00 96:00:00 F F 96.00
3,004:45 F 3 3004:45 3004.75
16,940:43 F 4 6940:43 6940.72

Time exported w/ space/comma, Convert to Number
 

Based on your original data col A as posted,
data assumed in A2 down

Put this in B2:
=LEFT(A2,SEARCH(":",A2)-1)+(MID(A2,SEARCH(":",A2)+1,2)/60)
Copy B2 down. Format col B to 2 dp. Tested here, seems to return exactly the
results that you indicated in your col E.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"al_ba" wrote:
I have a sample Data below...
A = original exported data, it has 1 space in front... for thousand, also
with space in front and with comma.
B = is my first step
E = result I need

Everything worked fine except when the Orig Data goes 10,000:00 and above,
just like the last data where I have 16,940:43; 6940:43; 6940.72...the first
digit is omitted, this goes with any data above 10,000:00.

Thank you in advance :)

A B C D E
Orig Data Remove Check for Convert Result Needed
Space Comma to Thousnd (Number Format)

6:30 6:30:00 F F 6.50
31:00 31:00:00 F F 31.00
975:00 975:00:00 F F 975.00
1,167:30 F 3 1167:30 1167.50
160:00 160:00:00 F F 160.00
31:00 31:00:00 F F 31.00
1,167:30 F 3 1167:30 1167.50
6,268:00 F 3 6268:00 6268.00
306:00 306:00:00 F F 306.00
96:00 96:00:00 F F 96.00
3,004:45 F 3 3004:45 3004.75
16,940:43 F 4 6940:43 6940.72

Time exported w/ space/comma, Convert to Number
 

.. return exactly the results that you indicated in your col E.
And with the error(s) you face for ".. orig data goes 10,000:00 and above"
corrected
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---

Time exported w/ space/comma, Convert to Number
 

Hi Max,

Thank you very much, this is perfect :)


"Max" wrote:

Based on your original data col A as posted,
data assumed in A2 down

Put this in B2:
=LEFT(A2,SEARCH(":",A2)-1)+(MID(A2,SEARCH(":",A2)+1,2)/60)
Copy B2 down. Format col B to 2 dp. Tested here, seems to return exactly the
results that you indicated in your col E.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"al_ba" wrote:
I have a sample Data below...
A = original exported data, it has 1 space in front... for thousand, also
with space in front and with comma.
B = is my first step
E = result I need

Everything worked fine except when the Orig Data goes 10,000:00 and above,
just like the last data where I have 16,940:43; 6940:43; 6940.72...the first
digit is omitted, this goes with any data above 10,000:00.

Thank you in advance :)

A B C D E
Orig Data Remove Check for Convert Result Needed
Space Comma to Thousnd (Number Format)

6:30 6:30:00 F F 6.50
31:00 31:00:00 F F 31.00
975:00 975:00:00 F F 975.00
1,167:30 F 3 1167:30 1167.50
160:00 160:00:00 F F 160.00
31:00 31:00:00 F F 31.00
1,167:30 F 3 1167:30 1167.50
6,268:00 F 3 6268:00 6268.00
306:00 306:00:00 F F 306.00
96:00 96:00:00 F F 96.00
3,004:45 F 3 3004:45 3004.75
16,940:43 F 4 6940:43 6940.72

Time exported w/ space/comma, Convert to Number
 

Welcome, great to receive such feedback. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"al_ba" <al_814 wrote in message
...
Hi Max,
Thank you very much, this is perfect :)