Query about find and replace

[ibwaheemi]

i am using openoffice calc to print out address labels for envelopes.

ideally i would like to put \n into the replace box or to put it another way i need to use the replace box to insert a new line within a cell

i am looking to replace commas with new line. at the moment i am manually replacing commas with <ctrl><enter>

PLEASE HELP

[Villeroy]

=SUBSTITUTE(A1;",";CHAR(10))

[RusselB]

Please note that Villeroy's suggestion doesn't change the information. but creates a second copy of the information with the change, so you end up having both.
If you want to use the Find & Replace dialog to change your original information, I believe entering , in the Find box and /n in the Replace box (ensure that the Regular Expressions box under More Options is checked) will do what you're looking for,
My knowledge of RegEx is limited, thus i don't guarantee that the information for the Replace box is exactly what you are wanting.

[ibwaheemi]

=SUBSTITUTE(A1;",";CHAR(10))

this method is very crude and actually creates more problems for me than it solves. i print the cells to a label printer, the cells are the same shape as the labels. the SUBSTITUTE formula changes the layout of the cell

also i need to substitute more than one character for the new line but " , " does not work i only want to remove the blank spaces before and after commas not all the blank spacesso i dont think substitute is sophisticated for me.

i should nention i am a total noob on formulas for openoffice i am very knowledgeable on ms excell formulas so i use that. but open office is easier for this task

---------------------------------------

Please note that Villeroy's suggestion doesn't change the information. but creates a second copy of the information with the change, so you end up having both.
If you want to use the Find & Replace dialog to change your original information, I believe entering , in the Find box and /n in the Replace box (ensure that the Regular Expressions box under More Options is checked) will do what you're looking for,
My knowledge of RegEx is limited, thus i don't guarantee that the information for the Replace box is exactly what you are wanting.

/n does not work in the replace box only the search box and also you mean \n and not /n
and yes i need to enter \n into the replace box or a shorthand for <ctrl><enter> that would be ideal and such a simple thing to do why doesnt openoffice have this functionality!

[keme]

If the comma-separated list is fairly consistent, this may work:
Instructions may cause major changes to your file, so save a backup first.

• Select your table and use Data - Text to columns.
• Tick the "Commas" box.
  You get a preview of the resulting columns in the pane at the bottom of the dialog.
• If the preview looks right, click the OK button.

Set up a new "print sheet" where you extract data from your original sheet, or at best register your spreadsheet as a database and do a "mailmerge" in a Writer document.