0

I'm trying to make a simple tool for my job where I can decline job listings for a specific reason simply by checking their boxes and then copying and pasting the final cell where it will compile all the reasons into a formatted response.

The spreadsheet

Hopefully the way the spread sheet works is relatively clear. I use a lot of CHAR(10)'s in this for formatting. all the cells in the N:N column are essentially formatted like =A1 &CHAR(10) &CHAR(10) to add spacing between everything.

The main problem is when I copy and paste the text from the final cell, I end up with ROWS of empty line breaks. I'm really not sure how this happens. I've tried the TRIM(), SUBSTITUTE(), and CONCATENATE() commands, removing the merged cells and the text wrapping, changing the cell formatting, and even removing a lot of the CHAR(10)'s (but not all) from the cell, but it still happens. I even remade this sheet in Google Sheets and it STILL did it.

The pasted blank space

What am I missing here??

Edit: These are the changes I attempted from @cybernetic.nomad it still doesn't seem to work properly. Even when I copy the "filler" version, I get similar excess space like in the picture above.

I changed the formatting here just so you could see how I wrote each formula.

First edit attempt.

Improve this question
6
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Nov 14, 2022 at 19:17
  • Consider using a placeholder character (or string) and once all the work is done, search and replace that character or string with char(10)
    – cybernetic.nomad
    Commented Nov 14, 2022 at 21:17
  • Replace all blank?
    – Lee
    Commented Nov 15, 2022 at 9:33
  • I need to better understand the structure of the grey "Merged Response" block you are copying from. It covers P4:U46. Is this merged to be a single "cell" or is it a 6 x 45 range of cells? You may be copying and then pasting a lot of cell breaks into your text document in the form of carriage returns.
    – Max R
    Commented Nov 15, 2022 at 20:43
  • @MaxR The "Merged Response" block is a large merged cell, not individual cells. I've actually tried unmerging and unwrapping this cell and copying from the single original cell and it still comes up with the blank space.
    – Nathan surv genorpg20
    Commented Nov 16, 2022 at 18:10

1 Answer 1

Reset to default
0

Apparently the problem WAS the merged cells. I tried copying from them while unmerged again and it worked this time. I'm not sure how I made this mistake last time. My solution now was to make a button macro to copy from a separate cell where the text can be formatted correctly in an single unmerged cell, and now the big block of text is just for looks.

Improve this answer
1
  • As a general rule, I would avoid merged cells wherever possible. They are a way to "pretty up" a spreadsheet but they complicate our logic in really un-intuitive ways. There are two things in Excel that I have essentially "sworn off" and won't do any more... Merged cells, and conditional formatting logic based on formulas embedded in the conditional format. I will always re-work the problem in a different way than resort to those.
    – Max R
    Commented Nov 16, 2022 at 22:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .