Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing hard breaks from comments or active cells
Dear All,
How do you remove hard breaks (returns) using a macro? I import files & text with hard breaks causes problems as our system doesn't allow such characters? TIA Julian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing hard breaks from comments or active cells
You can find and replace the hard breaks in any text by
looking for the corresponding character codes and removing them - but I would need to know for sure how your input text file was encoded. Most text files use the ASCII code 10 for a new line, but sometimes it is the code combination of 13, 10. To see exactly which codes your file uses, assign a small sample (including a line break) from one of your text files to the String variable txtInput and try this: For i = 1 to Len(txtInput) debug.print Mid(txtInput, i,1) & ":" & Asc(Mid (txtInput,i,1)) Next i This will list your sample text in the immediate window with each character followed by its code, and should allow you to find the line breaks and the exact codes used. Now all you need to do is eliminate those codes from your input string - if the line breaks use just the code 10, your macro could include a function as simple as this: Function RemoveBreaks(txtInput as String) as String RemoveBreaks = Replace(txtInput,Chr(10),"") End Function If you wanted to, you could even replace the line breaks with another character - the following will put a forward slash wherever there was a line break: RemoveBreaks = Replace(txtInput,Chr(10),"/") K Dales -----Original Message----- Dear All, How do you remove hard breaks (returns) using a macro? I import files & text with hard breaks causes problems as our system doesn't allow such characters? TIA Julian . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Manual Line Breaks in Multiple Cells | Excel Discussion (Misc queries) | |||
Removing hard returns | Excel Discussion (Misc queries) | |||
Hard breaks in text to soft breaks in Excel | Excel Discussion (Misc queries) | |||
Removing Hard Borders from Excel Spreadsheet | Excel Worksheet Functions | |||
Removing Page Breaks. | Excel Programming |