![]() |
Carriage returns - how to get rid of them (again)
A file with multiple columns contains a long text string in col B ...
this text contains carriage returns that I want to get rid of: ideal is to convert the text string in col B into several separate columns. here's the pain points: * the excel version I'm using is 2003 (OK) but German language UI (Yuk) * function "ersetzen / susbtitute" doesn't work * the carriage return doesn't respond to suggestions from previous posts: viz char(10) / Chr(10) or char(13) / Chr(13) Here's a sample of col B, where * represents the carriage return: 'A user has entered the following data in our subscription form:* * * Name: firstname lastname* * Country: antarctica* * E-mail Address: * * - example ends. and, no, sadly there is no DB to go back to, to do a re-export; this spreadsheet is all there is (sigh). all/any advice greatly appreciated |
Carriage returns - how to get rid of them (again)
I don't think Excel's search and replace capability will look for a
carriage return and replace it with another character. This utility will find a carriage return (ASCII character 10) and replace it with a space. Copy this code and paste it into your sprdsht as a macro, and let us know how it goes. Note that due to the vagaries of Usenet posting some unintended line wrapping may occur. This code tested fine on my machine; any errors may be due to line wrapping. Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub |
Carriage returns - how to get rid of them (again)
Saved from a previous post...
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" ","") '<--what's the new character, "" for nothing? If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Andrewsan wrote: A file with multiple columns contains a long text string in col B ... this text contains carriage returns that I want to get rid of: ideal is to convert the text string in col B into several separate columns. here's the pain points: * the excel version I'm using is 2003 (OK) but German language UI (Yuk) * function "ersetzen / susbtitute" doesn't work * the carriage return doesn't respond to suggestions from previous posts: viz char(10) / Chr(10) or char(13) / Chr(13) Here's a sample of col B, where * represents the carriage return: 'A user has entered the following data in our subscription form:* * * Name: firstname lastname* * Country: antarctica* * E-mail Address: * * - example ends. and, no, sadly there is no DB to go back to, to do a re-export; this spreadsheet is all there is (sigh). all/any advice greatly appreciated -- Dave Peterson |
Carriage returns - how to get rid of them (again)
If you actually have the CR character 0010 in column B you should be able to use
Text to Columns to get your columns. First insert several blank columns to the right of B Select column B and DataText to ColumnsDelimited byOther. Alt + 0010(on the NumPad) See what you get. Gord Dibben MS Excel MVP On Wed, 21 Mar 2007 07:01:23 -0700, Andrewsan wrote: A file with multiple columns contains a long text string in col B ... this text contains carriage returns that I want to get rid of: ideal is to convert the text string in col B into several separate columns. here's the pain points: * the excel version I'm using is 2003 (OK) but German language UI (Yuk) * function "ersetzen / susbtitute" doesn't work * the carriage return doesn't respond to suggestions from previous posts: viz char(10) / Chr(10) or char(13) / Chr(13) Here's a sample of col B, where * represents the carriage return: 'A user has entered the following data in our subscription form:* * * Name: firstname lastname* * Country: antarctica* * E-mail Address: * * - example ends. and, no, sadly there is no DB to go back to, to do a re-export; this spreadsheet is all there is (sigh). all/any advice greatly appreciated |
Carriage returns - how to get rid of them (again)
DaveO
EditReplace or Text to ColumnsDelimited byOther will both find the 0010 character. I think that OP may something other than CR's in his data if Alt + 0010 not working. Gord Dibben MS Excel MVP On 21 Mar 2007 07:51:52 -0700, "DaveO" wrote: I don't think Excel's search and replace capability will look for a carriage return and replace it with another character. This utility will find a carriage return (ASCII character 10) and replace it with a space. Copy this code and paste it into your sprdsht as a macro, and let us know how it goes. Note that due to the vagaries of Usenet posting some unintended line wrapping may occur. This code tested fine on my machine; any errors may be due to line wrapping. Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub |
Carriage returns - how to get rid of them (again)
hi Sandy many thanks for your support ... greatly appreciated your method worked fine ! my apologies for not getting back to you right away; once I had the method in place I had to get the job done *real* fast ... you know how it is sometimes! cheers, Andrew |
Carriage returns - how to get rid of them (again)
hi Dave many thanks for your support ... greatly appreciated in the event, Sandy's method worked fine my apologies for not getting back to you right away; once I had the method in place I had to get the job done *real* fast ... you know how it is sometimes! cheers, Andrew |
Carriage returns - how to get rid of them (again)
You're very welcome Andrew thanks for getting back to us.
Just to stop it driving me mad - what was the character in the cell? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Andrewsan" wrote in message ... hi Sandy many thanks for your support ... greatly appreciated your method worked fine ! my apologies for not getting back to you right away; once I had the method in place I had to get the job done *real* fast ... you know how it is sometimes! cheers, Andrew |
All times are GMT +1. The time now is 12:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com