Replace Hard Return In Excel Download The Demonstration

Would you like to learn more about Excel Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles).

Show Comments Excel Cell References Excel Dos and Donts - Removing Gridlines 2021, Spreadsheets Made Easy.

Why format a cell if it can be so easily undermined Thats the problem Aditee ran into with a distributed Excel template.

In this article, well discuss the problem and two simple solutions--neither of them perfect.

Replace Hard Return In Excel Download The Demonstration

You can work with your own data or download the demonstration.xlsx,.xls, and.cls files.

The first solution works in Excels browser edition; the VBA procedure doesnt.

SEE: 10 Excel time-savers you might not know about (TechRepublic) The problem: Excel paste task overwrites destination cells format Aditee collects data from several different vendors, who use an Excel template.

When entering date values, the templates formatting accommodates the YYYY-MM-DD format--it works as expected.

On the other hand, when someone pastes a date value, Excel ignores the destination cells format, retaining the formatting from the original source--not what users expect.

Aditee wants an automated process that will warn users when they enter data in the wrong format.

Data validation can reject non-date entries, but Ive yet to come up with a formula that also rejects date values that dont fit a specific format because the underlying value is a date serial value.

For example, Excel stores May 10, 2018 as 43230, regardless of its format.

You can change the cells format to text and use a custom rule to catch string values that dont match the format you want, but the inherent problem still exists: When the user pastes a valid date value, Excel accepts it as a date value--and your data validation rule is out the window.

You might try combining features such as data validation and conditional formatting, but again, Excel ignores your best efforts when a user pastes a valid date value.

If youre going to allow users to paste values, you must train them to paste only the values.

Regardless of how you enter the date May 10, 2018, Excel displays it as 2018-5-4, as shown in Figure B.

Figure A Apply a custom format to display dates in YYYY-MM-DD format.

However, if you paste a differently formatted date, Excel accepts the source formatting.

To further complicate the situation, Excel changes the format of the target cell to that of the pasted value In other words, after pasting the value in Date-formatted C4 into the Custom-formatted B4, B4s format matches that of C4--B4 is now a Date-formatted cell instead of a Custom-formatted cell, as you can see in Figure C.

Solution 1: Train the users The easiest way around this problem is to use Excels Paste Values option to paste only the values.

Lets use the same example to illustrate how this simple feature works: If youre following along, press Ctrlz to undo the last paste.

With C4 selected, press Ctrlc to copy the date value to the Clipboard.

Select B4, but dont press Ctrlv to paste the value as you normally would.

Figure D Paste Values copies only the value, so the target cells format retains intact.

Users might find it easier to use the Paste Options button instead: Repeat step 2 above.

When Excel displays the Paste Options button, click the dropdown, and choose Values ( Figure E ).

Replace Hard Return In Excel For Mac