Find and replace

[Guest]

Is it possible to replace with a caraige return?
I received a file with about 5000 words in one cell, separated by commas
every few words. Is there a way to use a "replace" to replace the commas
with a return so it breaks up onto about 1400 lines?

Or some other way to break it up into a usefull list?

 

[Ragdyer]

You could try this:

Find - enter a comma,
..In "replace with", hold down the <Alt> key,
And using the Num keypad, *not* the numbers under the function keys,
Enter
0010

Of course, you won't see anything in the replace box, but simply click
"Replace All".

The cell should automatically format to "Wrap Text",
But if it doesn't,
<Format <Cells> <Alignment> tab,
And click on "Wrap Text".

 

[Guest]

you could use a macro. paste the code into a VBA module and change the
source and destination ranges to suit your needs. if you are not familiar
w/macros there is some helpful material here
http://www.mvps.org/dmcritchie/excel/excel.htm


Sub test()
Dim varTemp As Variant
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = Sheets("Sheet1").Range("A1")
Set rngDest = Sheets("Sheet2").Range("A1")

varTemp = Split(rngSource.Value, ",", -1, vbTextCompare)

For i = LBound(varTemp) To UBound(varTemp)
varTemp(i) = Trim(varTemp(i))
Next i

rngDest.Resize(UBound(varTemp) + 1, 1).Value = _
Application.Transpose(varTemp)

End Sub