Date Formatting

[abacon1960]

I exported some financial data into a spread sheet that has the following in this format:

Col A Col B
Date $/Ton
2 14 2109 6,125.34
2 13 2019 6,345.45
etc.....

The dates are important as I want them to be used on a Chart so the formatting is important. As you can see the dates came in as 2 1 2109 with no backslashes or hyphens. I formatted the Col B to look like this 2-Feb-2019. Without manually retyping with the backslash each cell will not format.

Is there a way to highlight the column and have it format this way without manually retyping a thousand entries?

Thanks.

 

[Scott T]

You can create the date in a separate column and then copy it to column A as value.

=DATE(RIGHT(A2,4),LEFT(A2,SEARCH(" ",A2)),MID(A2,SEARCH(" ",A2)+1,2))

 

[steve the fish]

Try highlighting the column in question then pressing CTRL-H. Find what type a space. Replace with type a slash. Replace all. See if that works.

 

[abacon1960]

Amazing! Worked like a charm!

You can create the date in a separate column and then copy it to column A as value.

=DATE(RIGHT(A2,4),LEFT(A2,SEARCH(" ",A2)),MID(A2,SEARCH(" ",A2)+1,2))