Removing what looks like 'little boxes' from memo field

[HunterN]

I have searched the internet, but haven't found exactly the right thing that will work.

I have the database set up in ACCESS where there is seven fields and the last two fields for each record are Memo fields. They can contain a variable amount of data AND will be on different lines. When I transfer the record to Excel, they transfer exactly as I want them to on multiple lines within the cell, but the little boxes appear after each line. I am assuming these are Line Feed or Carriage Returns?

I have looked at suggestions on how to remove them by using the CLEAN function. But this puts them all on one line then. This is not what I want. They need to stay on their own line within the cell once in Excel. I have also looked at the SUBSTITUTE function, but haven't figured it out yet. Can anyone give me suggestions to this?

Thanks for any help with this!

Nancy

NAME	Size	StartPosition    StopPosition	  Action	EditedUniverse	ValidEntries
Test1	2	  951	              953	      N	     peage > 18	        "1
                                                                                 2
                                                                                 3"
Test2	2	  954	              955	      N	     peage = 18	        "-2
                                                                                  -1
                                                                                   0
                                                                                   1
                                                                                   2
                                                                                   3"

The above is what it looks like in Excel (of course when I copy to here the little boxes don't show! Also there is no quotes that show in my Excel, only when I copied it here)

 

[boblarson]

Yes, they are probably Carriage Returns. If the data has been typed in and the Control key is held down to insert a new line. You can try replacing using:

Chr(13)

or a combination of

Chr(13) & Chr(10)

 

[HunterN]

Bob,

This data is coming from a memo box on the Access form. The user hits the ENTER key to go to the next line to add more stuff, when they are done they hit the TAB key to go to the next text box. Then it gets saved to an Excel sheet. Also I have been viewing this data character by character and I can actually see there are two little boxes and they are chr(10). But if I do the following:

myStr = REPLACE (myStr, Chr(10), "")

it makes it go to one continues line in the memo box.

I thought chr(13) was a Carriage return. Are you saying that I should be replacing the chr(10) character with chr(13)? or the other way around?

Nancy

 

[boblarson]

Chr(13) is a carriage return
Chr(10) is a line feed

 

[HunterN]

This is my final code that gets rid of the little boxes that display in the Excel sheet.
But keeps it in the layout that I need them.

        For i = 2 To rst.RecordCount + 1
            sName = Range("H" & i).Value
            sName = Replace(sName, Chr(10), "")
            sName = Replace(sName, Chr(13), Chr(10))
            Range("H" & i).Value = sName
        Next i

Nancy

 

[xenou]

Note: Windows by default uses two characters for the end of a line:
Chr(10)Chr(13)
This is probably what you have in your original memo fields.

But for some odd reason, Excel uses a single character by default when you hit Alt+Enter in a cell to create a new line: Chr(10).

I think you just have to play with it until you stumble on a fix. You can find out for sure what character is in your text by copying it to a good text editor. Notepad++ is open source (free as in beer and free as in speech). With NPP++ you can use View | Show Symbols | Show End of Line to see what character(s) are at the end of the line.

My Excel (Access 2003/Excel 2007) displays the returns correctly when I export a memo field to Excel (with CRLF end of line characters) - so I can't really test this for you as I don't have a test case where this is happening to me.

(Note: MAC, Linux, and Windows all decided on a different default EOL marker - I guess just to keep us on our toes! Windows --> CRLF; Linux --> LF; MAC --> CR)

 

[HunterN]

My playing around resolved the problem.

The code I posted above was what I used.

Thanks for your suggestions Bob and Xenou

 

[boblarson]

Note: Windows by default uses two characters for the end of a line:
Chr(10)Chr(13)

But the order of them should be 13 before 10 (carriage return comes before line feed - think old style printers).

 

[xenou]

Dope...
Thanks. Yes, CRLF is correct. I must have been born just in time to use a manual typewriter as I remember doing that for real ...

I read an article a few months ago saying the very last maker of manual typewriters (a firm in India) closed their doors. Actual, hit the keys kind of machines - not even electric. End of an era.

 

[Norie]

Have you tried turning Word Wrap in Excel for the cells this data is being exported to?