remove string or space before date

[aris]

Dear friends


I have a column containing dates (e.g 22/10/1999...) and i would like to change the format of this date so i can match it with another dataset. I realised that i couldn't change the format my dates column because there is a space before the date in each cell and excel do not recognise it as a date.

Is there any way to remove the space [ 22/10/1999] before date in each cell and make it look like [22/10/1999]? I could do it one by one but my sample is almost 3000 observations.

Thank you very much

Aris

 

[Peter_SSs]

aris

You could try this:

1. Ensure that the column to the right of your dates is empty. (Insert a new column if required)
2. Select the column with the dates (and spaces).
3. Data|Text to Columns...|Delimited|Next|Tick 'Space'|Finish
4. Delete the old (now empty) column.

 

[al_b_cnu]

Hi,

Try highlight the column, then Edit / Replace and put a space in 'find What' and click 'Replace all'

 

[Peter_SSs]

Whoops! Missed the most obvious.

 

[eliW]

Hi

Just use: =DATEVALUE(A1)

And format as DATE

Eli

 

[aris]

Peter

Thank you very much for your help! Everything seems to work fine!!!

Best

Aris

 

[Peter_SSs]

Peter

Thank you very much for your help! Everything seems to work fine!!!

Best

Aris

Yes, but take note of Alan's - it's quick, simple and doesn't require an extra column.

 

[eliW]

IMPORTANT

Hi to all of you!

It seems that DATEVALUE function can handle all strings even those with preceeding spaces, that makes it very simple. Try it!

Eli

 

[Peter_SSs]

Re: IMPORTANT

Hi to all of you!

It seems that DATEVALUE function can handle all strings even those with preceeding spaces, that makes it very simple. Try it!

Eli

Eli, I don't have any problem with the DATEVALUE function, but for the problem posed by Aris, does not seem like the best solution to me. It requires an extra column and the formula would need to be repeated nearly 3000 times according to the information in the original post. If Aris is doing other work on the sheet, these 3000 formulas could be re-calculating regularly, which is a lot of extra processing that is not needed with either of the other two solutions.