carriage return code

[Rusty]

I imported a list of contacts from outlook. Many of the imported street
addresses have a carriage return followed by a PO box or suite number. I
want to search the street address field and move the text to the left of the
carraige return to another colum. Problem is, I don't know how to search for
the carriage return. Can you help me?

Thanks,

 

[David McRitchie]

Hi Rusty,

Excel only recognizes CHAR(10) or in VBA CHR(10) which is actually a LF (line-feed)
and in order for it to be effective you must have wrap turned on (format, cells, alignment, wrap text).
When you use Alt+Enter you automatically get wrapping turned on for that cell.

You could have CHAR(13) or in VBA CHR(13) which is CR (carriage-return)

or you could have a combination of the two CRLF it depends on what you were
actually provided.

To look for CHAR(10) using Find, Replace, or Text to Columns you
would hold Alt+0010 or
on a laptop use Fn+Alt+0010 on numeric keypad with NumLock off.
which will produce 1 character that you won't see.

If you don't know what the character that you actually have is you can test
with =CODE(A1) where A1 contains a single character
or with =CODE(MID(A1,15,1)) where the questionable chars is in position 15

If you have 09 as the code it is a TAB character

additional information in
http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm