How to remove characters from a cell

[Jonathan Dunne]

Hi all

I have a bunch of cells which contain both date and time e.g.

08/16/2005 18:38:32.300
08/16/2005 18:38:32.315
08/16/2005 18:38:34.308

I am wondering what is the correct procedure to remove the first 11
characters from each cell? I can't do a simple find and replace as the date
will change.

Thanks in advance
Jonathan

 

[Andibevan]

Dim AnyString, MyStr
AnyString = "08/16/2005 18:38:32.300" ' Define string.
MyStr = Left(AnyString, 11)

HTH

Andi

 

[Paul Black]

Hi Jonathan,

Try this Formula, Change the References Accordingly :-

=TRIM(RIGHT(A1,LEN(A1)-IF(ISERROR(FIND(" ",A1,FIND(" ",A1,FIND("
",A1,1)))),LEN(A1),FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,1)))-1)))

All the Best.
Paul

 

[Norman Jones]

Hi Jonathan,

If you want to display just the time in the original cells, format the cells
with a custom format:

hh:mm:ss

If you want to leave the original cells unchanged, but extract just the time
portion to another cell, then, in the destination cell enter the formula:

=A1-INT(A1)

where A1 is the original cell. Format the destination cells using the custom
format above.

If you want to convert the destination cells to static time values, select
the destination cells and Edit | Copy | PasteSpecial | Values.