Excel HELP Please

D

Dino Buljubasic

Hi,
One of the columns in my excel has its content broken into lines. I
guess each line within that column ends with a carrieage return and
line feed.

how can I replace these with say a "-" character? I was trying to use
Find and Replace but don't know how to enter the CRLf character and if
I use Alt 13 and / or Alt 10 (for cariage return and line feed) it
wont fine these.

Any help will be appreciated
 
D

Dave Peterson

If you see a little box, you may have both line feeds and carriage controls, but
my bet is you only have the line feed.

You can try this...
Select all the cells you want to fix.
edit|replace
what: ctrl-j (hit and hold the ctrl key, then hit j)
with: - (dash)
replace all

If 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),"-")

or as 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(13)) '<--What showed up in CellView?

myGoodChars = Array(" ") '<--what's the new character?

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

ps. Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm
 
D

Dino Buljubasic

hi Dave,

this worked just perfect :
You can try this...
Select all the cells you want to fix.
edit|replace
what: ctrl-j (hit and hold the ctrl key, then hit j)
with: - (dash)
replace all

Just one question, why Ctrl-j ? Does Ctrl - j represents Line feed
and Carriage Return chars in excel?

thank you
_dino_
 
D

Dave Peterson

If you want to force a new line in an excel cell, you can use alt-enter.

If you want to force a new line in a formula, you can use:
="asdf"&char(10)&"qwer"

(alt-enter and char(10) both represent line feeds.)

You could hold the altkey and use the numeric keypad to do alt-0010 (in the What
box of the find dialog).

But ctrl-j represents that same alt-0010 (j is the 10th letter!) and it's easier
to type.

char(13) is the carriage control. ctrl-m should be the same as alt-0013, but
ctrl-m is the same as the enter key--so it doesn't work.

In windows based stuff, there's a difference between the linefeed (char(10)) and
the carriage control (char(13)).

If you've used VBA, you may have seen:
vbLf
vbCr
and
vbCrLf

(line feed, carriage control and both linefeed and carriage control)
 
D

Dino Buljubasic

thanks dave

I appreciate your help

If you want to force a new line in an excel cell, you can use alt-enter.

If you want to force a new line in a formula, you can use:
="asdf"&char(10)&"qwer"

(alt-enter and char(10) both represent line feeds.)

You could hold the altkey and use the numeric keypad to do alt-0010 (in the What
box of the find dialog).

But ctrl-j represents that same alt-0010 (j is the 10th letter!) and it's easier
to type.

char(13) is the carriage control. ctrl-m should be the same as alt-0013, but
ctrl-m is the same as the enter key--so it doesn't work.

In windows based stuff, there's a difference between the linefeed (char(10)) and
the carriage control (char(13)).

If you've used VBA, you may have seen:
vbLf
vbCr
and
vbCrLf

(line feed, carriage control and both linefeed and carriage control)
Click to expand...
 
Top