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

"J45on" wrote in message
...
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.