Paste Values with LEFT and RIGHT functions

[Sam Pickard]

I have a column of dates pasted into my spreadsheet in the form of " 13APR12 AU" in Column A. I have used 2 helper columns with "=LEFT(A3,LEN(A3)-3)" in Column L and "=RIGHT(L3,LEN(L3)-3)" in Column M to get it into a format that Excel should recognize. I copy Column M and paste values into Column N expecting to be able to format as date and go on my way however it still shows up as "13APR12" no matter how I format it. If I click the formula bar and press enter it formats correctly, I have tried changing the formats before and after pasting and calculating sheet at different times in the process. I'm wondering if there is a way around this using a macro or anything.

 

[RonB1111]

Use just 1 helper column with this formula:

=--TRIM(LEFT(A3,LEN(A3)-3))

 

[p45cal]

Select the column of data to convert in column A
Data|Text to Columns
Delimited
Next
Space
Next
Column 1 - Skip
Column 2 - Date DMY
Column 3 - Skip
Destination N3
Finish.

 

[dave3009]

Hi and welcome

You can use text to columns for this. Highlight the data, goto Data>>Text to Columns>>Delimited>>Space>>highlight column with date in interface and choose Date then DMY from the drop down>>Any other columns you can highlight and choose Do not import (skip)>>Finish. Voila one column of dates perfectly foramtted.

KR


Dave

 

[Jeff Meyers]

Wrap the =DATEVALUE() function around your Column L formulas, and then format Column L for your preferred date format.

[L3] =DATEVALUE(LEFT(A3,LEN(A3)-3))

 

[Sam Pickard]

Thank You all very much for your quick responses, I definitely should have realized I could use Text to Columns for this instead of LEFT and RIGHT.

 

[Sam Pickard]

I notice that just putting "--" after the equals sign fixed the problem, what exactly is that doing?

 

[BenMiller]

I notice that just putting "--" after the equals sign fixed the problem, what exactly is that doing?

That converts the value to a number by making it a negative and then a positive. Similar to multiplying by 1 or adding 0.

 

[RonB1111]

The "--" is a quick way to tell excel the result of the formula is a value. A single "-" would make it a negative number, so two turns it back to a positive number. It does the same thing as DATEVALUE(), just less characters to type.