#### Dake

##### New Member
I imported a column of dates, some of which have a leading zero, like 09/21/2011. Since other dates don't have a leading zero (11/11/2011), the column doesn't sort chronologically. The various options in Text To Columns didn't work for me. Then I tried my old standby- copy and paste to Notepad, then back into a new (unformated) Excel column. Notepad rocks for getting rid of all formatting, allowing Excel to interpret the raw text correctly

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### 6StringJazzer

##### Well-known Member
Do you have a question?

#### Candyman8019

##### Board Regular
Give this a try: Assuming the date text is in cell AN3...
=IF(LEFT(AN3,1)="0",RIGHT(AN3,(LEN(AN3)-1)))

#### Candyman8019

##### Board Regular
I should have included the 'else' portion as follows: =IF(LEFT(AN3,1)="0",RIGHT(AN3,(LEN(AN3)-1)),AN3)

#### Dake

##### New Member

Do you have a question?
Actually I didn't, but now I do. My solution didn't work! Even though the leading 0s are gone, it still doesn't sort. I can't get these dates to sort correctly

#### Dake

##### New Member
Something weird's happening... here the formula I tried: =IF(LEFT(A2,1)="0",RIGHT(A2,(LEN(A2)-1)),A2) -just replacing your AN3 with my A2. When A2 = 09/06/2020, the result is 43714. I tried copy/paste the data as text into a new column...same result.

#### Fluff

##### MrExcel MVP, Moderator

Format the cell as a date.
43714 is 6th Sep 2020

#### Dake

##### New Member
Something weird's happening... here the formula I tried: =IF(LEFT(A2,1)="0",RIGHT(A2,(LEN(A2)-1)),A2) -just replacing your AN3 with my A2. When A2 = 09/06/2020, the result is 43714. I tried copy/paste the data as text into a new column...same result.

#### Dake

##### New Member
I finally noticed some improperly entered dates that were causing trouble, and things are sorting now. I don't know why the above formula didn't work- it looks accurate. Thanks everyone

#### jasonb75

##### Well-known Member
Without a bit more detail best guess would be that your original data was in UK date format but your regional settings are for US format (or vice versa). Your initial attempts with text to columns and notepad may have changed any dates where the day is no later than the 12th to a valid format but also flipped the month and day while the rest remain in the initial text format.

Try your original data (before you made any attempts to change it) with text to columns, making sure that you select the correct date format for your regional settings from the dropdown on the last screen, not the format of the dates in the document.

Replies
1
Views
65
Replies
8
Views
384
Replies
1
Views
335
Replies
8
Views
468
Replies
4
Views
93

1,126,939
Messages
5,621,723
Members
415,853
Latest member
Newlife72

### 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.

### Which adblocker are you using?

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

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