Contact details spreadsheet...!?!

[Fayebee]

Hi All,

I have inherited a sizeable spreadsheet from a predecessor and need to clean it up to enable me to convert it to a CSV file our our CRM system. The problem is, she has entered all of the telephone numbers into one column and i need to distinguish between landlines and mobiles. Is there a way of me having a formula that says something to the effect of 'if cell starts with 07 move to column P and if it starts 01 or 02 move it to column Q' This will take out all of the mobiles startng 07 and put them further along.

I dont know if this is possible, but i really dont fancy typing them all out, it will take me years!

Thank you so much in advance.

Faye

 

[Joe4]

Welcome to the Board!

Sure!

What column are they in now? Let's say it is column M. Then for row 2, you would put this formula in cell P2:
=IF(LEFT(M2,2)="07",M2,"")

and if cell Q2 you would put:
=IF(OR(LEFT(M2,2)="01",LEFT(M2,2)="02"),M2,"")

Then you would just copy those formulas down for the rest of the rows of data that you have.

 

[JimM]

Don't know if it's practical but could you sort by phone number so all the mobile numbers are together and then move en-masse

 

[Fayebee]

Columns are as follows
A - Pre Fx
B - Surname
c - Telephone number.

Thank you so much @Joe4, i really appreciate this

 

[Fayebee]

Don't know if it's practical but could you sort by phone number so all the mobile numbers are together and then move en-masse

Thankyou, i need to distinguish them into separate columns as the CSV upload will sort numbers into different areas on their CRM system accounts, if that makes sense.

 

[Joe4]

Columns are as follows
A - Pre Fx
B - Surname
c - Telephone number.

Thank you so much @Joe4, i really appreciate this

So then you should just be able to replace the column "M" reference in my reply with column "C".