Problem importing data from Access
 

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

Problem importing data from Access
 

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

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