vba - How do I remove Newline and or Carrige return characters in an Excel cell -

vba - How do I remove Newline and or Carrige return characters in an Excel cell -

i have excel sheet need convert csv file import database. excel sheet has 1 column of cells have homecoming characters, think because can't see there, causing csv file have records should apart of previous recordset. tried vba code around got of homecoming characters, code below.

public sub getcarriagereturn() dim mstring string activesheet lastrow = .cells(.rows.count, "a").end(xlup).row end = 2 lastrow mstring = cells(i, "b") lfposition = instr(mstring, vblf) 'chr(10) 'msgbox lfposition if lfposition > 0 mnewstring = left(mstring, lfposition - 2) & " " & mid(mstring, lfposition + 1, 20) trim (mnewstring) 'debug.print mnewstring cells(i, "b").value = mnewstring end if crposition = instr(mstring, vbcr) 'chr(10) 'msgbox crposition if crposition > 0 mnewstring = left(mstring, crposition - 2) & " " & mid(mstring, crposition + 1, 20) trim (mnewstring) 'debug.print mnewstring cells(i, "b").value = mnewstring end if next end sub

what missing here? assistance appreciated.

try this:

sub trythis() cells.replace what:=vbcr, replacement:=" " cells.replace what:=vblf, replacement:=" " end sub

it should scan cells in worksheet , replace both ascii10 , ascii13 blanks.

excel vba