Forum Discussion

Benbee250's avatar
Benbee250
Copper Contributor
Sep 16, 2022

ORGANIZE FILE

Hello,

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 

https://ibb.co/k41dp76ibb.co/k41dp76 

 

 

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    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
      • mtarler's avatar
        mtarler
        Silver Contributor
        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"

Resources