Problem importing data from Access

[BT Connect]

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]

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]

Many thanks, Dave

Jeff

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