I have a spreadsheet which is used to calculate a large table of data, which ultimately needs to be copied into another application. The numbers need to be formatted a specific way, some are formatted to 2 decimals, some to 1 decimal. In all cases, if the number >= 10, then only show the decimal if it is not zero. I have the cells formatted to their proper decimal places, and an overriding conditional formatting to remove the decimal from any whole number with 2 or more digits.

When I copy the data from Excel to paste into the other application, the values include the decimals which were removed by the conditional formatting. (e.g. shows as '12' in Excel, but when pasted it shows as '12.0')

I am working on a Mac, which doesn't have the number formatting option in the conditional formatting tab, but the formatting works properly. I set up the formatting on a PC which does have this option. I believe some of the values copied properly where others don't, I'm assuming the values which copy properly formatted were existing when the formatting was set on the PC, and any values which were changed on the Mac are not formatted when copied.

Is there any way to copy the values, including the conditional formatting, properly on the mac?

Your formatting in Excel only affects the display of the number. The underlying value does not change. This would affect any calculations as well since the decimals will factor in.

I think to change the value, you need to use one of the rounding functions.

Thanks for your response. I am aware that the formatting only changes the display, not the underlying value. What I'm trying to do is copy the formatted value (not the actual value), which works for the formatting applied to the cells. A cell has the value .6275 and is formatted to 2 decimals, displays in the spreadsheet as .63 and when copied the pasted value is also .63.

The problem is that I have conditional formatting applied to override this formatting for cells with a certain value. The formula I am using is:

The formatting applied to those cells is zero decimal places. And this works properly to remove the decimal from values that are 2 digits (>= 10) and the decimal value is 0. A cell with the value of 12.0 will display as 12 in the cell, but when copied, the pasted value is still 12.0, which is how the cells are formatted.

It looks like my only solution is to copy the cells into another temporary spreadsheet, and using Paste Special, paste only the values. This does seem to include the conditional formatting (e.g. 12.0 becomes 12). This is an extra step that I was hoping to avoid, although I should be able to script this at some point.

I'm sorry that I didn't understand. And I gotta be honest, I still don't. Would it be possible to attach the sheet. If that doesn't help, I'll reach out to some of the other folks here and see if they can help.

Attached is a demo file, notice that all cells are formatted with the custom formatting '0.0#'.

The Conditional Formatting removes the decimals from any other values that are 10 or more, as you can see in the first column. If you clear the custom formatting, from the file then you'll see the values as 98.0.

When I copy the top row, it comes out as:
What I'm looking for is:
While doing this, I found that the 'Paste Special' looks like it uses the actual value which has 4 decimal places for the last cell, so it comes out as
So, using the 'Paste Special' might not be the answer I'm looking for after all. I may have to come up with a scripted solution unless there's a way to do this otherwise. I've tried several of the options in the 'Paste Special' dialog, and didn't notice any that gave me the result I need.

Attached Files

CustomFormattingTest.xlsx (10.8 KB, 8 views)

- In Excel options - Advanced - Is Automatically add an insertion point checked or unchecked?
In my sheet it is unchecked and copy paste works perfectly

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

If I understand correctly, the values in your table are the result of a calculation?
Why not adapt this calculation to show the desired number of decimals ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

It is unchecked on my PC. I haven't had time to check where that option is on my Mac (which is where I will be using this data file). I have not been able to copy the data and paste it so that it shows up in the destination as it does in the spreadsheet.

The values are from a client supplied spreadsheet, and are not from a calculation.

I will be setting up couple of worksheets to do lookups from their data to organize it into the order that will be needed for the various views I will need, so I could probably add it to those lookup formulas, but those lookups/calculations may be complex enough without having to include the formatting in the calculated formula. Some of the columns of data need to be formatted to 2 decimals, others to 1. In all cases, the decimal should be removed based on the formula I was using for the conditional formatting.

I was happy that I was able to come up with a combination of cell formatting and conditional formatting that achieved what I needed, but the end result isn't the spreadsheet, this is a tool to organize their data to be pasted into an InDesign document, so I really need to be able to copy and paste it into that program on a Mac as it shows in the spreadsheet. And so far, nothing I have tried has done this.

What happens if you try the following :
select the range to be copied
put the arrow against the upper border of the selected range it becomes a cross
while holding down your right mouse button you can then move this range to another place on the sheet
when you let the button go, a menu will pop up
click " copy here as values only" and see if that's OK

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Still getting the decimal places that were removed with the conditional formatting
I've been testing this on my PC, when I have time, I'll try some of these variations on the Mac, where the copying and pasting will actually be done, but I don't expect to see any difference from the PC version.

Well, I never was able to get the conditional formatting to work properly, but I was able to solve my problem by updating the formula for the fields that I needed formatted. The following calculation does what I want:

IF([value]<10,

TEXT(ROUND([value],2),"0.0#"),

CONCATENATE(ROUND([value],2))

)


(the CONCATENATE part may not be needed, but in the larger formula that I was working with which combined the number with several text strings using another CONCATENATE statement, it didn't seem to work if it wasn't there)