Carriage Return multiple entries one cell

Hi,

This is my first post here. I'm looking forward to being able to help where I can and get help when I need it. At the moment I need some help. I've searched the net looking for a solution and haven't found one.

My basic problem is having more than one line of information in a cell. I'm required to keep email addresses and sometimes phone numbers and names in one cell for each row. But I'm also required to upload the data in an Access database. I'm getting tried of manually going through 3000+ records in Excel and manually fixing each one.

What I want to do is create a macro that will delete information I don't need and end up with one good email address per cell per row, which would be the first line. Each instance of information except the last (not always true) end with alt+enter (carriage return). I was looking for an inspect function on a string, but VBA doesn't seem to have that. Anyway, my data looks something like this.

--------------------------------
bill.smith@somewhere.com
bill.smith@somewhere.else.com
999-999-9999
--------------------------------
joe.smith@herethere.com
123 Main Street
Myplace, HM 9999
--------------------------------
joe.jones@therewhere.com
joe.jones@therewhere.here.com
Sec'y Janet Smith
--------------------------------
Empty
--------------------------------
bill.william@nowhere.com
--------------------------------

Can anybody help to get me started on this?

Thanks in advance.

JDB

The InStr function, looking for character code 10 (linefeed) should do it:

Please Login or Register  to view this content.

Well done T-J ...

The "instring" function is often forgotten although it is powerful and often more appropriate than mid() function ...

Carim

T-J,

Thanks for the reply. It works fine. Now I need some further help. The ActiveCell.Value function returns the entire contents of the active cell. The InStr function counts the first occurrence only. I need to read through the contents of the active cell and select what I want to keep and delete what is not needed. The carriage return and the "@" are key. Any line without an "@" goes. Would that be done with an array, or some kind of loop? I'm just not familiar enough with VBE to know where to begin. The help system doesn't give much in the way of ActiveCell.

JDB

The Split function is very useful, it splits a string into a variant array. Then combine it with the InStr function ::

Please Login or Register  to view this content.

Debug.Print is useful for debugging as it sends output to the Immediate Window in the VBE, to display it press Ctrl+G.
One idea would be to send valid email addresses to a listbox, allowing you to choose which one to keep.