Remove time from a text cell

NMaxis

New Member
Joined
Jul 14, 2016
Messages
19
Hi,
I'm looking for a way to remove time from a text cell so it just shows the other contents.

EX: 07/02/2019 9:00:30 AM CREATED to 07/02/2019 Created

Thank you for the help both in the past and now.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
How about
=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
 
Upvote 0
Power Query will do that for you with the following Mcode

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1.1", type date}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.1", type text}}, "en-US"),{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"
 
Upvote 0
can you tell us exactly what you want the cell to show? is it just "07/02/2019 CREATED" or is there something after CREATED?
 
Upvote 0
Try this.

=TEXT(DATEVALUE(LEFT(A1,SEARCH(" ",A1))), "dd/mm/yyyy") &MID(A1,SEARCH("m", A1)+1, LEN(A1))s
 
Upvote 0
There have been excellent solutions given to solve your problem.

My solution is assuming your data in Cell A1 and you want to extract the last part of your text:

=TRIM(MID(A1,1,FIND(" ",A1)))&" "& PROPER(MID(SUBSTITUTE(A1," ", "XY",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("XY",SUBSTITUTE(A1," ", "XY",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)+2,100))

As Cloyd1980 asked, if the last word of your text is always 'Created', much simpler solution will be:

= TRIM(MID(A1,1,FIND(" ",A1)))&" Created"
 
Upvote 0
Wit the string you supplied I get this


Book1
AB
107/02/2019 9:00:30 AM CREATED07/02/2019 CREATED
VBAinputs
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))


Is your string an accurate example of your data?
 
Upvote 0
HI,

Sorry for the lack of response I just got back to work after a bad accident. The formula from Fluff worked perfectly. It was various status' after the date not just created. Either or thank you for all your help.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top