#1   Report Post  
Old May 19th 05, 03:03 PM
J45on
 
Posts: n/a
Default Cell Format

I have two question/problems which i cannot solve. I have problems getting
Excel to interpret the data the way i see it. The data i am managing are
copied from a website directly through drag and pull style. It involves a few
thousand rows of informations and each of them posess the same problems below:

1. How do u format a custom formula so excel will recognise 1.12.6 as 1:12.6
(1 minute and 12.6 seconds)?
2. How do u sum up the data labeled 1.2L and 2.0L as 3.4L?

I can't make Excel to sum up by neglecting the letter 'L' as a side unit for
Length to add up to a sum of 3.4 . The problems cause me to take 3 times the
time spend on it jus to clear out a thousand entries of letter L and turning
1.12.6 into 72.6 seconds each typed out manually. A solution would be a great
help to me.

  #3   Report Post  
Old May 21st 05, 04:33 AM
J45on
 
Posts: n/a
Default

I'm not sure which problem are u referring u. My level of expertise are home
amature. It would be nice if u can tell me what i can type for the custom
format to be filled in the cell formating. But for the mean time i'll try to
determine and test what ur trying to say.

what is crtl+shift+enter for? what is array? i copy rather than import.
There are certain things that i want that excel did not import for me. So i
have to resort to manual copying that is by ctrl+C and ctrl+V from the
webpage to the spread sheet.

If it's too much of a hassle forget this thread and jus concentrate on my
1st two questions.

"Don Guillett" wrote:

on the import, try formatting before pasting

try this ARRAY formula which must be entered/edited with ctrl+shift+enter

=SUM(VALUE(LEFT(H1:H2000,LEN(H1:H2000)-1)))

--
Don Guillett
SalesAid Software

  #5   Report Post  
Old May 23rd 05, 06:48 AM
J45on
 
Posts: n/a
Default

Thx, i'll try to keep figuring it out.


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
Format a cell with numbers and user defined text Rod R. Excel Discussion (Misc queries) 0 March 30th 05 04:31 PM
Custom format that shows blank cell if another cell is empty Zdenek Moravec Excel Discussion (Misc queries) 1 March 25th 05 11:45 AM
format cell for figures - Ajit Ajit Munj Excel Discussion (Misc queries) 3 March 2nd 05 09:44 AM
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? Markus Obermayer Excel Worksheet Functions 1 January 4th 05 08:01 PM
How do I unhide the cell format function in Excel 2000 Len Melcer Excel Worksheet Functions 2 December 15th 04 06:49 PM


All times are GMT +1. The time now is 11:18 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017