Simple Excel Find/Replace

[apauto]

I have a large dataset in Excel that I want to upload.

One field has a "VbCrLf" in there. How can I do a find/replace on this?

Thanks! :)

[kevinashby]

Hi,

You could press (and hold) Ctrl and press "H" on your keyboard or go to the Edit menu and select Replace.

If you cannot see Replace on the menu you will need to expand it by either double clicking the edit menu, click the arrows at the bottom of the menu or simply wait a few seconds and it will expand by itself.

Hope this helps.

[JonW]

Excel find/replace can't find carriage returns (as far as i know). Word can by searching ^p.

For excel consider a basic function

public function TrimVBCRLF(Cell)

for i=1 to len(cell.text)
ch=mid(cell.text,i,1)
if ch <>VBCR and ch<>VBlf then TrimVBCRLF=TrimVBCRLF & ch
next i

end function

from macro menu slect visual basic editor. paste code in new module. close editor
then formula
=TrimVBCRLF(cellref)
should solve the problem, even if code isn't pretty.