Macro to search rows for specific text within a cell then cut cell to specified location

[jonathanpotts90]

Hi,

I'm new to all this macro creating and was wondering if someone could help me. I have a load of un organised addresses in rows (approx 6000):

House, Address 1, Address 2, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Address 3, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Address 3, Address 4, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Postcode, Tel, Fax, Email

All the addresses are different lengths so I need a macro (or formula if more applicable) to extract the cell with the email address in and then write it to a cell further over to the right. Luckily the Email, Tel and Fax all state 'Email', 'Tel' or 'Fax' in the cell, so this might be able to be used to sort.

So what I'm looking to end up with is:

House, Address 1, Address 2, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Address 3, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Address 3, Address 4, Postcode, Tel, Fax, Email
House, Address 1, Address 2, Postcode, Tel, Fax, Email

Help is much appreciated, thank you in advance.

Jonathan

[patel45]

can you attach a sample file (not 6000 rows, but 20) with before an after situation ?

[HaHoBe]

Hi, Jonathan ,

if all contents resides within one cell you could apply TextToColumns to split the contents up.

If you want to work with formulae you could utilize Find and search for the keyword (Find will deliver the position of the first character in that cell) and use the Mid-Function to filter the rest of the cell.

BTW: I use Firefox14, and I canīt make up a difference in both sections showing the data.

Ciao,
Holger

[jonathanpotts90]

Hi Holger

The data is each in individual cells.

What I want is to pull out the "email" cell and move it to the right, but the cell with the address is not consistently the same column.

Thanks

[HaHoBe]

Hi, Jonathan,

if the Email is either hyperlink or text including @ we can narrow down the range to look in to the columns G to J and run a macro on this range (will only copy the contents, if you need hyperlinks please come back):

Please Login or Register  to view this content.

Ciao,
Holger

[jonathanpotts90]

Hi Holger, that macro is great, how do I tweak it to search for cells not including certain text? Thanks a lot

[HaHoBe]

Hi, jonathanpotts90,

InStr delivers the position of the first character of the searched string inside to string and will deliver 0 if no match is found. So if no match is needed just change the greater sign to equal (from the code)

Please Login or Register  to view this content.

Please be aware that in the example the same column is used as I expected only one email to be inside each row. If more than one instance is found, either add to the cell like

Please Login or Register  to view this content.

Or use a counter if still in the same row to utilize Offset from the destination column.

Ciao,
Holger

[jonathanpotts90]

Ok that's great, last tweak, how can I get anything but the email and contact

think its this line that needs a list

If InStr(1, rngCell, "Email" "Contact") = 0 Then

[jonathanpotts90]

Ok that's great, last tweak, how can I get anything but the email and contact

think its this line that needs a list

If InStr(1, rngCell, "Email" "Contact") = 0 Then

Thanks