Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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 :)



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time/ Number format on excel file exported from Access Joe[_3_] Excel Discussion (Misc queries) 1 April 18th 07 02:12 AM
I need to add space to convert DATE to TIME format Wannano Excel Discussion (Misc queries) 9 April 11th 07 07:08 PM
add extra space before comma clarknv Excel Worksheet Functions 6 March 19th 07 04:52 PM
Inserting a space after a comma Visual Calendar Dilemma Excel Worksheet Functions 2 September 11th 06 11:20 PM
Can an Excel spreadsheet be exported to a comma-delimited import . jdebnam Excel Discussion (Misc queries) 1 April 21st 05 11:38 PM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"