AscII Characters (97)

Topic

New Reply

I have a .csv format file, which I am exporting from Access 97. Two of the fields are memo fields in Access and transfer with AScII characters (little square) for returns. How do I get rid of them prior to sending the file? Many thanks.

Blitz

Reply | Quote

Replies

Hi Blitz,

Before getting rid of them, you might try formatting the cells these fields go into as wrapped text. This may restore the original memo field formatting in your workbook.

Alternatively, if you need to get rid of them, then open the workbook and try doing a Search/Replace, typing Alt-010 into the ‘Find what’ box and nothing in the ‘Replace with’ box.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi thanks for the response – the transfer already seems to generate wrapped text. I have tried doing Alt 010 and Alt-010, with both the Alt key held down and released. It just seems to put a numeric value of “10” in, and nothing happens. I’m sure this is me making an ID 1 0T error (!) – what am I doing wrong?? Thanks Blitz

Reply | Quote

You hold down the alt key and type 010 on your numeric keypad. Then release the alt key. You’ll see nothing in the find box, but don’t worry, it will work.

Reply | Quote

You hold down the alt key and type 010 on your numeric keypad. Then release the alt key. You’ll see nothing in the find box, but don’t worry, it will work.

Reply | Quote

Pressing Alt+0010 will do the trick for the Line Feed character, but I don’t know how to do it for Carriage Return; Alt+0013 doesn’t work. Try this instead:

– Activate the Visual Basic Editor (Alt+F11)
– Activate the Immediate window (Ctrl+G)
– Type ActiveSheet.UsedRange.Replace vbCrLf, ” “ then press Enter.
– Switch back to Excel

If you need to replace only carriage returns or line feeds, you can use vbCr or vbLf instead of vbCrLf. And if you need to do this often, you can store the above instruction in a macro in a general module.

Sub Returns2Spaces()
ActiveSheet.UsedRange.Replace vbCrLf, ” ”
End Sub

Reply | Quote

Thanks folks! I’ll try this lot out.

Regards. Blitz

Reply | Quote

Thanks folks! I’ll try this lot out.

Regards. Blitz

Reply | Quote

Pressing Alt+0010 will do the trick for the Line Feed character, but I don’t know how to do it for Carriage Return; Alt+0013 doesn’t work. Try this instead:

– Activate the Visual Basic Editor (Alt+F11)
– Activate the Immediate window (Ctrl+G)
– Type ActiveSheet.UsedRange.Replace vbCrLf, ” “ then press Enter.
– Switch back to Excel

If you need to replace only carriage returns or line feeds, you can use vbCr or vbLf instead of vbCrLf. And if you need to do this often, you can store the above instruction in a macro in a general module.

Sub Returns2Spaces()
ActiveSheet.UsedRange.Replace vbCrLf, ” ”
End Sub

Reply | Quote

Hi thanks for the response – the transfer already seems to generate wrapped text. I have tried doing Alt 010 and Alt-010, with both the Alt key held down and released. It just seems to put a numeric value of “10” in, and nothing happens. I’m sure this is me making an ID 1 0T error (!) – what am I doing wrong?? Thanks Blitz

Reply | Quote

Hi Blitz,

Before getting rid of them, you might try formatting the cells these fields go into as wrapped text. This may restore the original memo field formatting in your workbook.

Alternatively, if you need to get rid of them, then open the workbook and try doing a Search/Replace, typing Alt-010 into the ‘Find what’ box and nothing in the ‘Replace with’ box.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote