Finding Cells with a Paragraph

[kparadise]

Hello,
I have to download an Excel document from our SOR every week, and then I copy and paste it directly into my Access database. There is one column in the Excel document which CAN contains multiple values which appears to have been entered after a paragraph (see below). Now this copies over to Access fine, but what I am trying to do is create a query in Access which will say; give me all the ID's that contains a Fruit column which a paragraph make (basically, give me all the IDs that have more than one fruit in it).

The character which separates the multiple values is a RETURN within excel and when I copy it over to Access. I found this by simply copy/pasting the cells to Word and then showing the paragraph symbols.

ID	Fruit
1	Apple
2	Banana
3	Apple Orange Kiwi
4	Banana Kiwi

<tbody>
</tbody>

 

[james_lankford]

try

select 
  ID, 
  replace(Fruit, Chr(13) & Chr(10), "-") as [Fixed Fruit] 
from 
  table1 
where 
(
  (
    InStr( 1, [table1].[Fruit], chr(10) ) > 0
  )
)

you might have to change the Chr(13) & Chr(10) to be just
Chr(13)
or just
Chr(10)