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.

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.

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

The formula was "obviously" for the sum with the L removed........

--
Don Guillett
SalesAid Software

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

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