Remove time from a text cell

[NMaxis]

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.

 

[Fluff]

How about
=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[NMaxis]

Kinda works but loses the status as well. Wanted like 07/02/2019 CREATED

 

[alansidman]

Power Query will do that for you with the following Mcode

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"

 

[cloyd1980]

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?

 

[Norie]

Try this.

=TEXT(DATEVALUE(LEFT(A1,SEARCH(" ",A1))), "dd/mm/yyyy") &MID(A1,SEARCH("m", A1)+1, LEN(A1))s

 

[Saba Sabaratnam]

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"

 

[Fluff]

Wit the string you supplied I get this

Book1
	A	B
1	07/02/2019 9:00:30 AM CREATED	07/02/2019 CREATED
VBAinputs

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

Is your string an accurate example of your data?

 

[NMaxis]

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.

 

[Fluff]

You're welcome & thanks for the feedback