Insert characters in a cell

[Ed Marvel]

I have a cell that lists the date as example 11/3/17
i would like to have it list as 11/3/2017 so I can format as a date showing November 3, 2017. Can not do with 2 characters in year field.
Any help greatly appreciated.
Thank you

 

[Rick Rothstein]

I have a cell that lists the date as example 11/3/17
i would like to have it list as 11/3/2017 so I can format as a date showing November 3, 2017. Can not do with 2 characters in year field.

If that is a real date value (that is, not a Text value), you can simply use cell formatting to make it look anyway you want... it is the first option listed for Date formats in the Format Cells dialog box (press CTRL+1 to bring this dialog box up).

 

[Ed Marvel]

The raw value imported in the examples 11/3/17 (Nov 3, 2017) or can be 5/12/17 (May 12, 2017)
I am pasting "Values only" into the adjacent cell
If I try to apply date format mmm,d,yyyy it gives an error "Cell contains a date string represented with only 2 digits for the year"

 

[Rick Rothstein]

The raw value imported in the examples 11/3/17 (Nov 3, 2017) or can be 5/12/17 (May 12, 2017)
I am pasting "Values only" into the adjacent cell
If I try to apply date format mmm,d,yyyy it gives an error "Cell contains a date string represented with only 2 digits for the year"

I have never seen that error message generated by the Cell Format dialog box nor can I do anything to make it appear, so I am not exactly sure how to tell you to proceed. Try this and see if it makes it work... select the cells in the column you pasted the values to, then call up the Text To Columns dialog box (Data tab, Data Tools panel)... select "Delimited" on the first dialog page, make sure the comma option button is not selected and click the "Finish" button. Now try applying the Custom Format you indicated you wanted and see if that works.

 

[Ed Marvel]

I tried listing it as a date format but it would not take it. It may have to do with the cell containing a formula to strip all data after the date.
I started with "10/1/17 11:59:52 PM CDT" then used used "=LEFT((A3),(FIND(" ",(A3),1)-1))" in adjacent cell.

Thank you

 

[Ed Marvel]

Solved
I found a way to do what I need although not very clean.
I started with the imported format as "10/1/17 11:59:52 PM CDT" in cell A3
I applied the following formula in cell B3 to get just the date "=LEFT((A3),(FIND(" ",(A3),1)-1))" This stripped the trailing text starting at first space leaving just the date but the in the wrong format.
In cell C3 I applied the formula "=DATEVALUE(B3)". Cell C3 had a custom format of "mmmm d,yyyy" which got me to the format I wanted. October 1, 2017
I appreciate all the help

 

[Rick Rothstein]

I tried listing it as a date format but it would not take it. It may have to do with the cell containing a formula to strip all data after the date.
I started with "10/1/17 11:59:52 PM CDT" then used used "=LEFT((A3),(FIND(" ",(A3),1)-1))" in adjacent cell.

You are trying to format the cell with the formula, correct? That formula is returning a Text value, not a date. If you add zero to it... that will convert it from Text to a numerical value (dates to Excel are just floating point numbers where the whole number is the number of days offset from the day before January 1, 1900 and the decimal part is the time represented as a fraction of a 24-hour day)... then you will be able to format the cell the way you want. Here is your formula with zero added to it...

=LEFT((A3),(FIND(" ",(A3),1)-1))+0


EDIT NOTE
--------------------
If you want to format the original "date" before you apply any formulas, use what Fluff has posted in Message #8 .

 

[Fluff]

Try
Select col A > Text to Columns > Delimited > Next > Select Space > Next > select Date > In preview (at the bottom) select the other columns > select Do not import column > Finish