Use InStr to find 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

 

[Guest]

Try search for Chr(13) or Chr(10)
InStr(1,[Field1], Chr(13))

 

[John Nurick]

Seee my response in the programming group
(microsoft.public.access.modulesdaovba) - and next time please do not
post the same question independently to multiple groups!

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]

Thank you very much for responding to my question. I do appreciate your help.

Sorry, I posted my question in more than one place for three reasons. First,
because I couldn't decide what group would be most appropriate. Second,
because it seems that different people read/respond to the different groups.
I only post a few questions a year. Sometimes I don't get any response in
one group so I try a different group. Third, being the weekend and needing
to figure this out ASAP, I didn't wait to post to an additional group. And
as it turned out, I got three different responses all with helpful
information.

At your suggestion, I won't do it again.

Thank you,
Judy

Seee my response in the programming group
(microsoft.public.access.modulesdaovba) - and next time please do not
post the same question independently to multiple groups!

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]

Thank you very much for your response. If you want a laugh, I had even tried
InStr(1, [Field1], vbCrLf) which, of course, did not work.

I look forward to trying your suggestion when I am back at work tomorrow.

Thanks again,
Judy

Try search for Chr(13) or Chr(10)
InStr(1,[Field1], Chr(13))
--
Duane Hookom
Microsoft Access MVP

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