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

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


with @tiscali.co.uk


"Andrewsan" wrote in message
...
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

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

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

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

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

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

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

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