How to remove "Chr(13)" or "enter" from a cell, by VBA code or by a function. Attached a needed example.
I must to remove "enters" and to save column structure of the strings.
Thanks in advance.
Remove "Chr(13)" from a cell
-
-
-
Re: Remove "Chr(13)" from a cell
Hi jonny
you shouldve uploaded an "excel" file...
anyway
to remove spaces ("enters") you may use the TRIM function
to remove the thirteenth charachter you may use a function such as
=CONCATENATE(LEFT(A1,12)&RIGHT(A1,LEN(A1)-12))
where the string is located in Cell A1
The same can be easily replicated in VBA
HTH
pangolin
-
Re: Remove "Chr(13)" from a cell
Pangolin, thanks a lot for a prompt response.
It also may be solved by "=CLEAN(A1)". You solution is nice , but does not care for next case (see attachment).
-
Re: Remove "Chr(13)" from a cell
Hi Jonny,
May be..
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),"")
HTH
-
Re: Remove "Chr(13)" from a cell
Hi,
For code you could use[vba]Range("a3").Replace vbcrlf,""[/vba]
-
Re: Remove "Chr(13)" from a cell
How can I replace double "enter" by one??
Thanks
-
Replace of double "enter" in a cell by one??
How can I replace double "enter" by one??
in Excel: function "clean()" removes all "enters".
in VBA: str = Replace(str, vbCrLf, "") removes all "enters" also -
Re: Remove "Chr(13)" from a cell
Jonny - please stick to one thread for related questions. I have merged the two threads. Thanks
-
Re: Remove "Chr(13)" from a cell
Range("a3").Replace vbcrlf & vbcrlf ,vbcrlf
-
Re: Remove "Chr(13)" from a cell
Andy, thanks.
It works!
May I put condition , that in a case of one "enter" do:
Range("a4").Replace vbCrLf , ""
and in a case of two "enters" do:
Range("a4").Replace vbCrLf & vbCrLf, vbCrLfThanks
-
Re: Remove "Chr(13)" from a cell
[vba] If InStr(1, Range("A4").Text, vbCrLf & vbCrLf, vbTextCompare) > 0 Then
Range("a4").Replace vbCrLf & vbCrLf, vbCrLf
Else
Range("a4").Replace vbCrLf, ""
End If
[/vba]
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!