Date and Text in one field

[hatstand]

Hi All,

I have a folder of files imported into a query, unfortunately one of the fields is a Date field that has text, namely 'YTD'. So the field is converting to 'Any'. Converting this into Dates obviously gives errors, but I can't fathom out how to hold both Text and Dates in one field. Any ideas?

 

[CountTepes]

How are you importing the data and what is the format of the imported YTD field date?

After importing you could use the formula =mid(A1,4,len(A1)) This assumes that the date with YTD is in A1 and has a space after YTD
Copy the formula for each row that has the YTD date

Then select the calculated values copy and paste specials, as values only back over the cells with YTD

Alternatively do a search and replace

Press ctrl-H
Search for YTD replace leave blank

If you are using VBA to import the data then you can strip off the YTD either on the fly,or at the end using the following algorithm

for each cell in range
     cell.text = mid(cell.text,4,len(cell.text))
next
range.numberformat = "dd/mm/yyyy" ' or whatever format you need the date.

 

[hatstand]

My apologies, I should have said I'm actually using Power Query to import. A handy solution that I'll keep though, Thanks.

 

[sandy666]

could you post any example of your source data and expected result?