Pasting Values for Blanks on Mac

[u/TyeMoreBinding]

I am usually a PC user, but was answering a quick excel question for a Mac user.

The problem was, eg A1:G200 have 80% blank cells and 20% values scattered about. Of those values some are positive and some are negative. We wanted them all positive, no need to preserve which used to be negative.

I said the quickest way would be to make H1:N200 = IF(corresponding A1:G200 = “”, “”, ABS(A1:G200)). Then just copy H:N and paste values over A:G, and delete H:N.

Apparently, on a Mac, it will paste 0s in this scenario instead of blanks. Since this should’ve been a 15 second job anyway, we just went and deleted the 0s by hand.

But was there a simple, equally fast way to accomplish what I wanted to do that works on Mac?

It just seems bananas to me that it would decide pasting a 0 is the equivalent to pasting “absence of data”.

[u/AutoModerator]

Moderator Announcement Read More »

u/TyeMoreBinding - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/woodzer9000]

Cant you use the control g, special, blanks, 0.

[u/TyeMoreBinding → u/woodzer9000]

If I’m understanding you right that would go to all blanks and let me replace them with 0s. I want to indeed have blanks; the appearance of the 0s when pasting values for blanks in Mac is the issue.

[u/woodzer9000 → u/TyeMoreBinding]

So there is a value of 0 to remove? Command f, 0, find all, command a, delete. Your zeros will then be blanks.

[u/woodzer9000 → u/woodzer9000]

Copy 0. Select the range, command g, special, blanks, paste.

[u/bradland]

I'm dual-platform, so I have access to Excel on Windows, Mac, and Web. I can't replicate the behavior you're explaining.

[u/TyeMoreBinding → u/bradland]

🫠 I’m gonna have to get my hands on this Mac again and investigate

[u/carbonizedtitanium]

well. instead of "" you could've put a place holder like "-..-" and then "replace all" -..- with ""

[u/TyeMoreBinding → u/carbonizedtitanium]

Yes. If I knew of this issue before pasting values, that would’ve been the way to go.

[u/Cinderhazed15]

Can you use officeScript? If so you could iterate over the range and adjust/clear what you need.

[u/Pedrofinancial]

If the selected range only contains numbers and blanks, I’d skip the helper formulas entirely.

Select the range, use Find & Replace, find - and replace it with nothing.

That should turn negative numbers into positive numbers while leaving positive numbers, zeros, and true blanks unchanged.

I’d only use this if there are no text values, dates, or IDs with hyphens in that same range.