I have an Excel spreadsheet saved from nn Access database that had carriage
returns in it. How can I now automatically remove these from Excel?
Search-and-replace does not work.
Thank you,
Mike O.
I have an Excel spreadsheet saved from nn Access database that had carriage
returns in it. How can I now automatically remove these from Excel?
Search-and-replace does not work.
Thank you,
Mike O.
Find hold down alt and type 010 on the numpad, replace with space or nothing
should work or run a simple macro like
Sub Clean_Carriage_Return()
Selection.Replace What:=Chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub
Regards,
Peo Sjoblom
"Mike O." wrote:
> I have an Excel spreadsheet saved from nn Access database that had carriage
> returns in it. How can I now automatically remove these from Excel?
> Search-and-replace does not work.
>
> Thank you,
>
> Mike O.
Mike
What are you searching for?
Usually CR's are CHAR 10 or CHAR 13
Edit>Replace
what: ALT + 0010 or 0013 from the NumPad
with: nothing or a space
Hold the ALT key and type 0010 on the NumPad(at right side of keyboard)
You won't see anything in the box, but it is there.
If this doesn't bring you joy, post back.
There are other methods.
Gord Dibben Excel MVP
On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike
O.@discussions.microsoft.com> wrote:
>I have an Excel spreadsheet saved from nn Access database that had carriage
>returns in it. How can I now automatically remove these from Excel?
>Search-and-replace does not work.
>
>Thank you,
>
>Mike O.
I have tried all of these steps and nothing seems to work. I am doing a
copy/paste from outlook 2003 to excel 2003. The street line with more than
one line sends all the data after to the next line and if there are 3 lines
it gets bumped yet again.
"Gord Dibben" wrote:
> Mike
>
> What are you searching for?
>
> Usually CR's are CHAR 10 or CHAR 13
>
> Edit>Replace
>
> what: ALT + 0010 or 0013 from the NumPad
> with: nothing or a space
>
> Hold the ALT key and type 0010 on the NumPad(at right side of keyboard)
>
> You won't see anything in the box, but it is there.
>
> If this doesn't bring you joy, post back.
>
> There are other methods.
>
>
> Gord Dibben Excel MVP
>
> On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike
> O.@discussions.microsoft.com> wrote:
>
> >I have an Excel spreadsheet saved from nn Access database that had carriage
> >returns in it. How can I now automatically remove these from Excel?
> >Search-and-replace does not work.
> >
> >Thank you,
> >
> >Mike O.
>
>
I've never gotten Char(13) to behave nicely in the Edit|Replace dialog.
Saved from a previous post:
Chip Pearson has an addin that can help you find out what is exactly in that
cell.
http://www.cpearson.com/excel/CellView.htm
If it turns out to be "nice", you can use Edit|Replace
what: alt-xxxx (use the numbers on the number keypad--not above the
QWERTY keys)
with: (spacebar) or whatever you want.
This can work nicely with alt-enters (alt-0010), but will fail with other
characters (alt-0013 for example).
You could use a macro to clean them up:
Option Explicit
Sub cleanEmUp()
Dim myBadChars As Variant
Dim iCtr As Long
myBadChars = Array(Chr(yy), Chr(zz))
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr
End Sub
Change the yy/zz to what Chip shows (and you can drop ", chr(zz)" if you only
have one offending character).
(And I changed them to space characters.)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
joe smith wrote:
>
> I have tried all of these steps and nothing seems to work. I am doing a
> copy/paste from outlook 2003 to excel 2003. The street line with more than
> one line sends all the data after to the next line and if there are 3 lines
> it gets bumped yet again.
>
> "Gord Dibben" wrote:
>
> > Mike
> >
> > What are you searching for?
> >
> > Usually CR's are CHAR 10 or CHAR 13
> >
> > Edit>Replace
> >
> > what: ALT + 0010 or 0013 from the NumPad
> > with: nothing or a space
> >
> > Hold the ALT key and type 0010 on the NumPad(at right side of keyboard)
> >
> > You won't see anything in the box, but it is there.
> >
> > If this doesn't bring you joy, post back.
> >
> > There are other methods.
> >
> >
> > Gord Dibben Excel MVP
> >
> > On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike
> > O.@discussions.microsoft.com> wrote:
> >
> > >I have an Excel spreadsheet saved from nn Access database that had carriage
> > >returns in it. How can I now automatically remove these from Excel?
> > >Search-and-replace does not work.
> > >
> > >Thank you,
> > >
> > >Mike O.
> >
> >
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks