Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and replace
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and replace
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". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "widman" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and replace
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 "widman" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to cancel a find & replace command "midstream"? | Excel Worksheet Functions | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
VB Find and Replace | Excel Worksheet Functions |