Find and Replace "Enter Button"

  • Thread starter Thread starter Keith Ward
  • Start date Start date
K

Keith Ward

Hi,

How do I find and replace a carriage return?

My problem is two fold.

I have an excel file that was exported from access. The description field
seems to have a double carriage return where there was only one in access.

Also the descriptions where imported into access by copying and pasting from
a PDF document and so they contain carriage returns where it shouldn't
because the text was wrapped in the PDF document.

So I get The following:

High quality, budget priced 3 band

radio cassette recorder with auto

timed silent recording.

FM/MW/LW wavebands, dial

tuning, digital clock/alarm, wake

up to radio, buzzer or cassette.

Microphone, headphone socket.

Internal batteries (not inc.) or

220/240V mains.

I first want to replace all the carriage returns with a space so I get and
then replace the full stop with a full stop and carriage return. So I can
get text I can manipulate as I wish.

So so do a find and replace carriage returns with a space?

Thanks

Keith
 
Keith

If you have carriage returns in a cell you should be able to see them if you
turn off "text wrapping" on that cell.

You will see a square box at each return.

Try Edit>Replace

what: ALT + 0010(on numpad)
with: space(or nothing)

OR ALT + 0013

If this fails, try the CLEAN Function.

Post back if more is needed. There are a few other avenues of approach.

Gord Dibben Excel MVP
 
Hi Gord,

I'm using Excel 2004 on a Mac PowerBook so don't have the number pad. Tried
the Alt + 0013 & 0010 but could not find it.

The clean function works well only some of the descriptions have been added
after the import from the PDF and so are actually OK.

I cannot perform a find and replace on the cleaned cells as its a formula,
correct?

The only reliable method of cleansing I can seem to find is the replace of
the double carriage returns with the space.

Some sentences will end in ? And ! So I will miss them replacing just the .
And carriage return.

Any more Ideas on replacing the double carriage returns with a space?

Thanks

Keith
 
Keith

See inline

Hi Gord,

I'm using Excel 2004 on a Mac PowerBook so don't have the number pad. Tried
the Alt + 0013 & 0010 but could not find it.
Click to expand...

Can't help you there.
The clean function works well only some of the descriptions have been added
after the import from the PDF and so are actually OK.

I cannot perform a find and replace on the cleaned cells as its a formula,
correct?
Click to expand...

You could Copy and Paste Special>Values to get rid of the formulas.
The only reliable method of cleansing I can seem to find is the replace of
the double carriage returns with the space.

Some sentences will end in ? And ! So I will miss them replacing just the .
And carriage return.

Any more Ideas on replacing the double carriage returns with a space?
Click to expand...

Macro........

Sub Remove_CR_LF()
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

This will add 2 spaces where you have double returns.

Run this macro to clear out extra spaces when done with first macro.


Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub


Gord
 
Back
Top