Wanted: A Formula to alter the way a cell's contents are displayed

[rbion]

I was given a file with thousands of records. In one of the columns, the information in each cell has to be presented differently to the viewer. A single formula needs to be written that will change the existing contents of a cell to look like a list (ie. each item within the cell is followed by ALT + Enter so they line up one under the other). Note that each cell has several items within it and they’re separated by the # symbol and a few random numbers. Any of these extra characters that separate the items need to be removed as well.

I don’t want a macro as I’m only learning more complex formulas at this time and don’t want to get into macros yet. Attached is a sample as well as what the outcome should look like. Any suggestions would be very welcome!

[scott.s.fower]

i think you're out of luck if you don't want to use VBA because there is no way that I know of to replace characters with alt+enter

[Jonmo1]

This will get you started (mind you it's quite a task your asking)

Highlight column A
Press CTRL + F
Select the Replace Tab
In the Find What box, put #*#
In the Repalce with box, Hold down the ALT Key and type 010 then relase ALT
Click Replace All

[ChemistB]

I think Jonmo's technique will work but only on a traditional keyboard with a keypad to the right. I've never gotten it to work on a laptop keyboard. Correct me if I am wrong, Jonmo.

[rbion]

Thank you all - I was able to try it on both a standard keyboard and a laptop (just to see the difference) and now that the most difficult part of my problem has been resolved, I can complete the rest of the task. I've also learned about non-printing characters such as a line break aka ALT + 010 - I didn't even realize that was the issue.

[Jonmo1]

I think Jonmo's technique will work but only on a traditional keyboard with a keypad to the right. I've never gotten it to work on a laptop keyboard. Correct me if I am wrong, Jonmo.

Good point, I think you're right.
I've never tried it on a laptop, but I know it works only on the keypad to right on standard keyboard. Not with the numbers on top of keyboard.