Search string for Excel carriage return

[Guest]

I am importing an Excel spreadsheet into Access. There are several columns
where a carriage return has been used to separate the data in the field. I
would like to use the InStr command to find the posistion of the newline
character--which appears as a small square when I'm looking at the text field
in Access--so that I can extract the data into separate columns.

I tried using InStr(1,[Field1], "\n"), this did not work. I could sure use
some help with this.

Thank you,
Judy

 

[Alex Dybenko]

Hi,
I don't remember exactly what character is there, but try:

InStr(1,[Field1], chr(10))
or
InStr(1,[Field1], chr(8))

else you can just get it number as:

ascii(mid([Field1],7,1))

if newline char at position 7 for example

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Nurick]

Hi Judy,

VBA doesn't recognise escaped characters in strings, so you have to
call Chr(10) or pass the predefined constant vbLf.

Unless execution speed is an issue, I probably wouldn't bother with
InStr() and the resulting calculations. Instead, I'd call this little
function, which works fine in an Access query:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)

End Function

e.g. to get the first line from the string in field , use

SafeSplit(, Chr(10), 0)

If you ask for Item(3) and there are fewer than 4 lines in ,
SafeSplit just returns a Null. If in that situation you want an empty
string instead, use

Nz(SafeSplit(, Chr(10), 0), "")

I am importing an Excel spreadsheet into Access. There are several columns
where a carriage return has been used to separate the data in the field. I
would like to use the InStr command to find the posistion of the newline
character--which appears as a small square when I'm looking at the text field
in Access--so that I can extract the data into separate columns.

I tried using InStr(1,[Field1], "\n"), this did not work. I could sure use
some help with this.

Thank you,
Judy

 

[Guest]

John,

I was trying to use the InStr function along with combinations of Right$ and
Left$. Your SafeSplit function will make what I am trying to do a whole lot
easier! I am very thankful that you happened to read and respond. I can
hardly wait to try it out tomorrow when I return to work.

Thank you,
Judy

Hi Judy,

VBA doesn't recognise escaped characters in strings, so you have to
call Chr(10) or pass the predefined constant vbLf.

Unless execution speed is an issue, I probably wouldn't bother with
InStr() and the resulting calculations. Instead, I'd call this little
function, which works fine in an Access query:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)

End Function

e.g. to get the first line from the string in field , use

SafeSplit(, Chr(10), 0)

If you ask for Item(3) and there are fewer than 4 lines in ,
SafeSplit just returns a Null. If in that situation you want an empty
string instead, use

Nz(SafeSplit(, Chr(10), 0), "")

I am importing an Excel spreadsheet into Access. There are several columns
where a carriage return has been used to separate the data in the field. I
would like to use the InStr command to find the posistion of the newline
character--which appears as a small square when I'm looking at the text field
in Access--so that I can extract the data into separate columns.

I tried using InStr(1,[Field1], "\n"), this did not work. I could sure use
some help with this.

Thank you,
Judy