Excel Data Sorting

[ollyfsxz]

Hello kind people not sure if anyone is able to help me at all been trying to sort this for hours but don't think I'm quite up to the level needed any help would be greatly appreciated.

So I have data in a spreadsheet;

- Owner Name
- Business Name
- Contact Number 1
- Contact Number 2
- Email Address
- Website Link
- Location

Problem is a lot of the information is scrambled from when it was scraped for instance some of the list has say 3 contact numbers so all the information is in incorrect columns been trying to play around with 'IF' statements but nothing seems to be working I was thinking the easiet way would be 'IF includes "@" move to a new column' same for all the other stuff for instance 'IF starts with 0 move to this column'

Any help would be super appreciated.

Many Thanks,
Olly

 

[Tim_Excel_]

Sub movetocol ()

With Workbooks(REFERENCE).Sheets(REFERENCE)

    For each c In .Range(RANGE) 'for each cell in this range

        If c.Value Like "*@*" Then 'if the cell value contains "@"
            .Cells(c.Row, "COLUMN").Value = c.Value 'copy the value of the cell in the current iteration to the cell on the same row but in column x
            c.Value = "" 'clear the cell in the current iteration
        End If

    Next c

End With

End Sub

Change the text in caps

 

[ollyfsxz]

Hi @Tim_Excel_

Thanks so much for your fast reply I'm just currently at work for another 1 hour 30 will try when I get home if I find it to be of any problems can I drop you a message?

Many Thanks again!!

 

[ollyfsxz]

Can I please ask where I input this code? iv'e opened the developer tab and have selected the VBA.

Is it insert User Module?

Many Thanks,

 

[Tim_Excel_]

Correct

A useful lengthy tutorial on the basics of VBA

 

[ollyfsxz]

Sorry Tim,

I'm a little confused please see my attached file to see if it makes a little more sense to you.

Many Thanks,

[/URL][/IMG]

 

[Tim_Excel_]

The code I provided will, if you set the range right, loop through the cells of ONE column and look for cells containing "@". If you set the target column right, it will clear the source cell and paste its value into the target cell.

This means you will have to tweak my code to not only work for one column but also to check for other values, such as (phone) numbers. This most likely means you will do a loop for each column necessary.

I won't provide you with a complete code as that will cost more time than I want to spend on this and since you will not learn to handle such problems yourself that way. I advice you to look into for each... loops and finding/changing cell values.