find and replace data

[hoomantt]

Hi Dear Friends
i have an excel files.there are in column D , Date Data.(ie, 2014/12/01 or 2013/02/04 or ...)there are more than 4000 Rows.
that data comes from more than 20 excel files from several cities.
members who send these files to me are idiot and they can not sen that data correctly.for example they send Date Datas : 2014-12-05 or 05/12-2014 or 20141205 2014.12.05 and ... that these formats not more than 50 formats.
i thought that i can extract these data and make a table in another sheet of this file .
put false data in column A and type right data in column B.(a table from A1 To B50)

for example
Column A Column B
2014-12-05 2014/12/05
05/12-2014 2014/12/05
20141205 2014/12/05
2014.12.05 2014/12/05
.....

Now i just want a formula Or a VB Code to find false data in sheet1 column D and replace with right data match with false data in column B.

 

[NARAYANK991]

Hi Tehrani ,

It will be better if you can upload your working file , so that all the formats are taken care of ; each format will need to be treated separately , either using formulae or VBA.

Narayan

 

[rumshar]

Hi Tehrani,
Why not send a Template with date validation to all of them? One more option is use google form. Have you given them a try?
With Regards
Rudra

 

[Xen]

I have very (very) rough solution for you.
It works providing:
Month is always in the middle no matter how bad formatting is.
All years are 4 char, months and days are 2.
Year is 2014 (you can modify it easily to look for more, I just was lazy here)

As I am not real hardcore excel Pro, it's not very elegant and I guess some of the parts could have been done easier, but well

Some explanation on what was done:
Datevalue function can help you convert some not so badly formatted text into dates. But somehow it does not like actual dates, so I check if there is an actual date entered (I didn't find isdate() function or something similar, so I check if Year() returns a number, it might be a bad way to do this check, so be careful here), if we still get errors after Datevalue() and Year(), then we strip cell from punctuation (,./- and spaces) and look where "2014" is, so we know in which order we should assemble our date.
As you can see, it is not very good and might not fit every case. And I see there might be a problem with years 2001-2012 if you modify function to look for other years, as 2012 might be December 20th I hope you only need 2013 and on

 

[json]

is it possible for you to create a template spreadsheet for them to all use? that way you can apply/control which style formatting you want.... just a thought

 

[hoomantt]

is it possible for you to create a template spreadsheet for them to all use? that way you can apply/control which style formatting you want.... just a thought

hi dear friend,
can you please help me to make a template that for example in column A , they can not enter any data without ( YYYY/MM/DD ) Format?
please make it for me and upload any where for me to download it.Please.
Thanks alot

 

[NARAYANK991]

Hi Tehrani ,

Can you say what is the default date format on your computer ?

On my computer , the default date format is mm/dd/yyyy , but when I enter a valid date in the format yyyy/mm/dd , Excel automatically converts it to the mm/dd/yyyy format. I can enter a date in the format yyyy/mm/dd and have Excel display it in that format only if I first format the cell as Text , or enter the data prefixing it with a single quote '.

Narayan

 

[Xen]

Here it is.

 

[hoomantt]

Hi Tehrani ,

Can you say what is the default date format on your computer ?

On my computer , the default date format is mm/dd/yyyy , but when I enter a valid date in the format yyyy/mm/dd , Excel automatically converts it to the mm/dd/yyyy format. I can enter a date in the format yyyy/mm/dd and have Excel display it in that format only if I first format the cell as Text , or enter the data prefixing it with a single quote '.

Narayan

hi dear friend
that is YYYY/MM/DD