How to alter the format of a referenced cell: from multiple lines/cells to one line.

Hi

I have searched for the answer but I must not be using the correct terminology because I cannot find an answer.

A brief background: I am using Word for printing customs documents and address labels but I am trying to streamline my operations using excel.

Currently I copy and paste an address into one word document for the address label and then print it. I then paste the address again into another document and run a simple macro that finds the paragraph marks and replaces them with a comma. This has the effect of changing an address from being written on separate lines to all being on one line which is what I need for the customs documents.

The idea I had was to have a master sheet that I paste the address into only once and then the address would automatically be referenced in two other sheets, one for the address label and one for the customs document.

I have no problem getting the address label set up because the address is already in the correct format. The problem I am having is finding a way to get the referenced address/cell from the master sheet to fit correctly in the customs document. It needs to be changed so it does not have any carriage returns or "hard returns".

Does anyone have an idea of how it can be done please?

Thank you.

Thanks Jeff for the quick reply.

I am not sure that is going to work.

I have attached the sheet that I am making to try to make things clearer.

The cell in the last sheet called "Customs" is the one I am having trouble with. I need to get the address to fit into that space somehow.

Isn't this what you described? I applied my formula to your label cell on Customs. See attached.

Hi Jeff

My apologies, that is exactly what I was looking for.

Thank you for helping so quickly with this, it is really appreciated

You're welcome!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."