Date Formatting

abacon1960

Board Regular
Joined
Jul 28, 2014
Messages
52
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,630
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You can create the date in a separate column and then copy it to column A as value.

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,586
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top