Remove part of pasted information in cells?

[Vihral]

Hi everyone!

Im trying to get a sheet to automatically fill in based on the raw data i paste into other sheets. this part is not a big issue but the issue is that in one of the raw data columns the time stamp is not for example "08:09:12" like a regular one, Its "08:09:12sec".

Is there any formula that I can add to a column that will automatically remove the "sec" part which would then make it possible to add other values to get a total amount of time? or will i manually have to remove the "sec" everytime?

Thank you in advance!

[FDibbins]

try this...

=LEFT(A1,LEN(A1)-3)*1

[Vihral]

Hi, Not sure how to implement that part. The current one im running is:

Please Login or Register  to view this content.

How can I implement that line into what you wrote?

[Ghozi Alkatiri]

if data at A1 formula at B1

=SUBSTITUTE(A1,"sec";"")

[Vihral]

Hmm, Im very new to Excel and programming so not sure HOW to implement that either if im honest

I tried setting it straight into the cell but then I dont know how to grab my value and If i set it in another cell then the line just comes back as not working.

[Vihral]

Heres an example file.
example file.xlsx

Typed in 2 values as regularly which works fine and then 2 values with the sec text added to it. This is the kind of file I want to use but Im not sure on how to get it to work.

[FDibbins]

you have an extra space at the end of some of the entries. try this version instead...
=LEFT(A1,LEN(TRIM(A1))-3)*1

[Ghozi Alkatiri]

use array formula

{=SUM(--SUBSTITUTE(A1:D1;"sec";""))}

see this attachment

[Vihral]

IT WORKS! Thank you so much!

[FDibbins]

not sure which 1 worked, but we are happy to help. please consider adding the reputation to those that helped (see point 3 below)