Ive got an excel sheet like with data in A1 like this 2013-05-24 16:55:04, i want to be left with just the time, hh:mm:ss part of that string.
Is there a way i can write in cell B1 somthing like = cut first 11 chars off A1 ?
Best Answer
if it is stored as text, then
=MID(A1,12,LEN(A1)-11)would work
If it's a date/time value then you have
=A1-INT(A1)to return the value as a number, or
=TEXT(A1,"hh:mm:ss")to return the value as text
If the string you have is really a date/time, you might be better off with:
=TEXT(A1, "hh:mm:ss")What happens if you replace *_ (asterisk space) with nothing?
This should work
=RIGHT(A1,LEN(A1)-11)
Try the MID function explained here
If you can't be bothered to read that, here's the syntax
= MID ( Text , Start_num , Num_chars )
You can use the MID or RIGHT function. MID would be better, I think. The syntax would be something like: =MID(A1,12,LEN(A1)-11.
How the formula works
MID takes 3 arguments:
the input string, or a reference to a cell that contains the input string
the starting character that MID will return
the number of characters to return.
So, A1 holds the input string, the first character to return is the 12th one (skipping the first 11), and the length of the string to return is just the length of the complete string minus the 11 characters that are being skipped.