Help in excel Urgent, 1

[karsh]

I have a excel spreadsheet with following example
ADIS WAVEHEIGHT $ ULTRASONIC TRANSCEIVER $J/B

this is one row data, whereever there is $ sign, i want the text after that go to next line WITHIN THE SAME ROW CELL.
as i have thousands of line, can anybody suggest a way to make this work,i am trying marcos and other functions, but nothing is seeing to work.
I will really appreciate if you help me with this.
THANKS ...

 

[gizmo1973]

You could try text to columns with the $ as your delimeter as long as the amount of $ signs in each row of data are the same

Regards, Phil.
C.E.O. Bodgeit, Leggit & Scarper. International.
"Stuffing things up completely since 1973"

 

[Zathras]

After performing Text to Columns with "$" as the delimiter, you can re-concatenate with Char(10) values:

for example, assuming "ADIS WAVEHEIGHT $ ULTRASONIC TRANSCEIVER $J/B" is in cell A1:

Select column A.
Do Text to Columns.
Insert a new column A.
Insert this formula in A1:
=B1&CHAR(10)&C1&CHAR(10)&D1
Copy down all rows with data.
Copy and paste special values.
Format the column for Alignment with word wrap.

If not all cells have the same shape, you can either set up the formula with the max case and live with extra lines, or make a more complex formula with IF statements. Post back if you need more help.

 

[mwhalen]

Is there a way to automatically have Excel split out into different worksheets the info based on different values in a column?

 

[xlbo]

mwhalen - in what way is your post related to this thread??
Please start a new thread with a pertinant and descriptive title

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

You could also avoid Text to columns altogether and just do an Edit / replace on your data, replacing $ with (010). You would need to put the $ in the 'replace what' field and then in the 'replace with' field hold down ALT and type 010 on the NUMBER pad. It should automatically wrap, but if not just format / cells / alignment / wrap text.

You may want to play with replacing _$_ with $, _$ with $ and $_ with $ initially to save having any erroneous spaces at the start/end of your data.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

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

 

[karsh]

thanks for the input.......
i was able to get it with the function
=SUBSTITUTE(A1,"$",CHAR(10))