# delete variable number of texts in row of cells



## bucci35 (Dec 18, 2022)

Hi,
I have a spreadsheet that has a from of cells that look like the following


10/14/2022 12:39​9/24/2022 15:01​9/21/2022 17:10​9/11/2022 16:51​3/10/2021 10:55​2/24/2021 10:41​
I'm trying to just keep the dates and get rid of the times all the way down and am having a hard time figuring this out. Any ideas would be greatly appreciated!
Thanks,
Dan


----------



## Micron (Dec 18, 2022)

You should be able to simply format as one of the Date formats. If that's not working, then your values could be text. To test that, change one of the cells to number and see what the sample looks like. Below, chosen format is number, sample looks the same.





If it remains the same, your values are text data type and not true dates (which are numbers) thus I don't think applying a date format will work on its own. If you wrap those values in DateValue function: =DATEVALUE("10/14/2022 12:39")

and format as date without time, you should be able to hide the time portion. If you have a lot of these, rather than type the formula in many times, use another column and then you can drag down a formula like =DATEVALUE(A2)


----------



## bucci35 (Dec 18, 2022)

Ok the value does not remain the same. I'm confused now as to what the formula should be in the column next to it to drag down. Assuming the first cell I"m going after is C4, what would the formula look like in D4 etc.
Thanks
Dan


----------



## Micron (Dec 18, 2022)

What does the value look like if you choose number?
When you drag a formula down, you don't have to do anything else. I suppose C4 would look like =DATEVALUE(B4), or A4, or wherever your dates are.


----------



## bucci35 (Dec 18, 2022)

ORIGINAL CELL 12/17/2022  7:01:06 PM
CHANGE TO NUMBER FORMAT IT LOOKS LIKE - 44912.7924305556


----------



## Micron (Dec 18, 2022)

Are you shouting??
I say the problem is that at least some your date time values are text (probably because they're imported from somewhere) and can't be converted - except that the example you just posted is a valid date. Values that don't convert, like 9/24/2022 15:01 are not valid dates, because it can't be determined if 15:01 is 3:01 PM or 15 minutes and 1 second.

If you don't need the time values at all, try text-to-columns and choose date for the dates column, then delete the columns/data that is the time portion.




Date portion is now a date.
Alternatively perhaps, fix the import, assuming there is one.


----------



## MARK858 (Dec 18, 2022)

If in 2 empty columns you put the formulas below (changing the C4 to the first cell in your range) and drag both columns down,  do you get TRUE in every cell in the ISNUMBER column and a 5 figure number in the INT column (or a date with 00:00 time)?


```
=ISNUMBER(C4)
```


```
=INT(C4)
```


----------



## Peter_SSs (Dec 18, 2022)

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another way if you want the dates (without times) in the next column

Select C4 to the bottom of your data
Data ribbon tab -> Text to Columns -> Delimited -> Next
Select the 'Space' delimiter (and make sure 'Other' is deselected) -> Next
Enter D4 in the 'Destination' box and also select the second column at the bottom and choose 'Do not import column (skip) up the top as shown below. Then click the 3rd column at the bottom and also select 'Do not import ...' at the top
Click Finish





Here is my sheet after that process (my dates are d/m/y format)

22 12 19.xlsmCD123414/10/2022 12:3914/10/2022524/09/2022 15:0124/09/2022621/09/2022 17:1021/09/2022711/09/2022 16:5111/09/2022810/03/2021 10:5510/03/2021924/02/2021 10:4124/02/202110Remove Time


----------



## bucci35 (Dec 19, 2022)

Wow, that worked perfectly! Thank you so much...BTW, in regards to Micron's comment about me yelling, I selected caps at that time because I lost my glasses and couldn't see during that reply! LOL 
Again thanks so much!

Dan


----------



## Peter_SSs (Dec 19, 2022)

bucci35 said:


> Wow, *that *worked perfectly!


Not sure what "that" refers to as there were a number of workable suggestions made in the thread, but in any case we're glad you have a solution. Thanks for letting us know.


----------

