Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community


Copper Contributor


I have an excel file with 2k names/phone numbers and addresses. I need to organize the file to make sure the phone numbers are in one column all landline phones and in the other all mobile.  

Attached a link to what i mean You can see that there is a mix of landline phone numbers and mobile in each column. I want to find a way to make it only mobile and only landline in its own column 




6 Replies
you have multiple phone numbers on a line so if there are 2 land lines and 2 mobile numbers you want just the 1st of each or are you suggesting adding columns so you have something like
Phone0_MobileNumber, Phone0_LandNumber,... Phone1_MobileNumber,Phone1_LandNumber,...
If the latter then simply add 2 columns for each phone number section (and name accordingly) and add formulas like: =IF(S2="Mobile",R2,"") and =IF(S2="Land Line",R2,"") is the corresponding columns and adjust the cell accordingly.
better yet would be if the whole set is defined as a table (Format as a Table) and then use the table reference [@[Phone0_Number]] instead of the R2 cell reference


what i mean is i want it to be organized like this sample attached.

want all the landlines in one column and mobiles. 

@Benbee250 The link to your original file is broken, could you please re-post?  


Okay I see the link was pasted twice and I can now see the picture but it would be helpful to have some data to work with instead.  I think it's an easy solution but need to test.  

as dexter noted, it would be easier if we had a sample sheet (personal/confidential info stripped).
but as I noted above a simple IF statement could work. In each of your NEW "Landline" columns you have IF(S2="Land Line",R2,"") where S2 and R2 which are for "Phone0" to the columns for "Phone1" ... and the same thing for new "Mobile" columns except ="Mobile"


So, This is the original. this link is what my file looks like


I would like to have the landlines and mobile in order but in the same row if that makes sense. 

So like Landline 1| Landline 2...   Mobile 1| Mobile 2.... l to look like this .


If needed send me your email i can send you the file to test. 

if you click on my name you can send PM and should be able to attach a file that way