Carriage returns - how to get rid of them (again)

[Guest]

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: (e-mail address removed)*
*

- 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

 

[Sandy Mann]

Try:

=CODE(MID(<cell Reference with the text>,62,1))
(or whatever it is in German)
it should return 109, the code for the letter "m" the last letter in the
first row of text.

Now try

=CODE(MID(<cell Reference with the text>,63,1))

which should return the next character after the "m"


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[DaveO]

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

 

[Dave Peterson]

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

 

[Gord Dibben]

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 Data>Text to Columns>Delimited by>Other.

Alt + 0010(on the NumPad)

See what you get.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

DaveO

Edit>Replace or Text to Columns>Delimited by>Other 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

 

[Guest]

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

 

[Guest]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk