sum text&numbers

[puiuluipui]

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.

 

[Sheeloo]

Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

 

[Rick Rothstein]

To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss

 

[Shane Devenshire]

Hi,

Try this:

=SUMPRODUCT(--SUBSTITUTE(A1:A2,"late ",""))

assuming your two enteries are in A1:A2. On the plus side of this fomula is
that is will work for 100 cells just as well as 2. Format the cell to time.

 

[puiuluipui]

Thanks!

"Rick Rothstein" a scris:

To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss

--
Rick (MVP - Excel)

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.

 

[puiuluipui]

Thanks!

"Shane Devenshire" a scris:

Hi,

Try this:

=SUMPRODUCT(--SUBSTITUTE(A1:A2,"late ",""))

assuming your two enteries are in A1:A2. On the plus side of this fomula is
that is will work for 100 cells just as well as 2. Format the cell to time.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[puiuluipui]

Thanks!

"Sheeloo" a scris:

Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.

 

[puiuluipui]

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Sheeloo" a scris:

Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.

 

[puiuluipui]

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Rick Rothstein" a scris:

To put the entry in the cell as text...

="late "&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss")

To put the entry in the cell as a real time value...

=RIGHT(A1,8)+RIGHT(B1,8)

and Custom Format the cell using this...

"late "[hh]:mm:ss

--
Rick (MVP - Excel)

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.

 

[puiuluipui]

Hi, i have one more problem. your code it's working but someytimes i have a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Shane Devenshire" a scris:

 

[Rick Rothstein]

Try this formula...

=IF(OR(A1={"","No"},B1={"","No"}),"","late
"&TEXT(RIGHT(A1,8)+RIGHT(B1,8),"[hh]:mm:ss"))

--
Rick (MVP - Excel)

Hi, i have one more problem. your code it's working but someytimes i have
a
blank cell or a cell containing "NO", and it gives me #VALUE!

Can the code ignore blank cells or cells containing "NO"?

Thanks!

"Sheeloo" a scris:

Try this in C1
=RIGHT(A1,8)+RIGHT(B1,8)
and format the cell as [h]:mm:ss

Hi, how can i sum 2 cells that contains each, text and numbers.
Ex:

A B
late 00:00:05 late 00:02:48


(late 00:00:05)+(late 00:02:48)=late 00:02:53
or
(late 00:00:05)+(late 00:02:48)=00:02:53


Thanks in advance.