Posted to microsoft.public.excel.misc
|
|
Problem importing data from Access
Many thanks, Dave
Jeff
"Dave Peterson" wrote in message
...
Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm
If it turns out to be a single line feed character (char(10)), then you
can use
alt-0010 (hit and hold the alt key while typing 0010 using the numbers on
the
keypad keys--not above the QWERTY keys).
If it turns out to be the carriage return (char(13)), or a combination of
keys
(crlf?), then you could use a macro to clean up the characters (turn one
to ""
(nothing) and turn the other to an easily typed, but unused character (!
or |).
Then use data|text to columns specifying that character.
Option Explicit
Sub cleanEmUp()
Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?
myGoodChars = Array("","|") '<--the new characters?
If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
BT Connect wrote:
When trying to import some data (names and addresses) from an Access
database, the Excel (2003) import wizard shows a "square" character
between
the elements of the Street Address data. I presume these are carriage
return
and line feed characters of some sort (?)
How can I enter this character in the Other delimiters box to split out
the
different elements of the address?
Any other suggestions would be gratefully received!
Jeff
--
Dave Peterson
|