eBay
  • Daily Deals
  • Sell
  • Customer Support

eBay Community

THE ANSWER CENTER

The Answer Center is your place to ask fellow eBay Community members questions about buying and selling on eBay, and for you to share your best information, tips, and insights to help other members get answers to their own questions.

View All Questions

Choose a topic to view

GROUPS

Groups is a great place to connect with other community members who share similar interests. Give support, share information, and connect with fellow members. Create or join a Group today!

CHOOSE A GROUP TO VIEW


Announcements

The latest news and updates affecting eBay buyers and sellers

Go Now

EBAY COMMUNITY POLICIES

Our policies are designed to create a safe and fair environment for all eBay members. Learning what’s allowed can help you avoid unintentionally breaking the rules and helps everyone in working with reliable, trustworthy members.

Go Now
Showing results for 
Search instead for 
Do you mean 

My Profile

Welcome!
PEOPLE YOU FOLLOW

Connect with Fellow eBay Community Members!
Register or Sign In Today!

Reply
Community Member
Posts: 3,305
Registered: ‎10-04-2004

Remove CRLF from Excel cell?

Hi, can I do a simple search & replace (Control+H) within an Excel cell to remove all the CR & LF from the cell? I could remove the CRLF from the HTML if a copy/paste to a text editor and bring it back in, but it'd be nice if was possible to do it inside of Excel w/o macros or VBA code.

Thanks. _______
TekGems: via vt6421a
Mr. Sun - TekGems.com
Please use plain text.
Community Member
Posts: 859
Registered: ‎01-30-2006

Remove CRLF from Excel cell?

in reply to tekgems
i dont know about the Ctrl H thang.. I will try to remember to try that one day...

Just add a button or user form... and set the code.. Its a few lines of code.. I even posted it on this board before.. One click its done.. in Excel..

I just dont understand where you are going with this... or Why even.. To eliminate the xls? perhaps?
--------
Chris G
Please use plain text.
Community Member
Posts: 153
Registered: ‎10-18-2006

Remove CRLF from Excel cell?

in reply to tekgems
tekgems,

i don't think you can put a carriage return into the search box.

how about:
=substitute(urText, concatenate(char(13), char(10)), "")

it's not toooo much of a pain
Please use plain text.
fun_n_save
Community Member
Posts: 1,267
Registered: ‎12-29-2006

Remove CRLF from Excel cell?

in reply to tekgems
On the spreedsheet where we keep the listing information, we dragged a button and assigned a VBA macro.

Sub ReMoveCR()
ToLoop = 0
Do Until Selection.Value = ""
t = Selection.Value

t = Replace(t, Chr(10), "")
Selection.Value = t
Selection.Offset(1, 0).Select

If ToLoop = 0 Then
xAns = MsgBox("Continue looping?", vbYesNo + vbDefaultButton2, "REMOVE CR")
If xAns = vbYes Then
ToLoop = 1
Else
Selection.Offset(-1, 0).Select
Exit Do
End If
End If

Loop
End Sub

We move the cursor to the description cell and click the button. It gives option to do just one cell, or continue to do all below.

fun
Please use plain text.
Community Member
Posts: 3,305
Registered: ‎10-04-2004

Remove CRLF from Excel cell?

in reply to tekgems
hey ny - Control+H is just search & replace.

I just used the built-in CLEAN function to remove the CR & LF. Then copy & pasted the CLEAN column into a new column using Paste Special (Value).

I think you mentioned in other threads this is an easy way to remove carriage return & line feeds from Excel cells. _______
TekGems: via vt6421a
Mr. Sun - TekGems.com
Please use plain text.
Community Member
Posts: 859
Registered: ‎01-30-2006

Remove CRLF from Excel cell?

in reply to tekgems
There is a better way to do it (if your doing whole column) in one shot).

Where the 13 is.. thats counting how many columns over..Column M You may have to change that to where your description is.


Sub cleanup()
Dim TheCell As Range

For Each TheCell In ActiveSheet.UsedRange.Columns(13).cells
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
--------
Chris G
Please use plain text.
Community Member
Posts: 1
Registered: ‎11-16-2009

Re: Remove CRLF from Excel cell?

in reply to tekgems
You can search for a CRLF by typing their corresponding ANSI sequences in: ALT+0013 for the CR and ALT+0010 for the LF. Since they aren't visible characters, you won't see them after typing them in, but Excel will find them just fine.
Please use plain text.