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 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

[BT Connect]

Many thanks, Dave

Jeff


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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