Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

 Cust ID | Cust Name   | Phone Number | Email            | Address     |

|--------|-------------|--------------|------------------|-------------|

| 1      | John Doe    |(123) 456-7890|john@example.com  | 123 Main St |

| 2      | Jane Smith  |(234) 567-8901| jane@example.com | 456 Oak Ave |

| 3      | David Brown |(345) 678-9012| david@example.com| 789 Elm Blvd|

 

Task :

1. Split the Phone Number column into Area Code, Prefix, and Line Number using Text to Columns feature.
2. Use SUBSTITUTE function to remove special characters from the Email column.
3. Concatenate Address, City, State, and Zip Code columns into a single Address column.
4. Use TRIM function to remove leading and trailing spaces from the Customer Name column.
5. Create a data validation dropdown list for selecting different customer names to view details.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1. Split the Phone Number Column into Area Code, Prefix, and Line Number:
   • Select the Phone Number column.
   • Go to Data > Text to Columns > Delimited > Next.
   • Choose Delimiters (in this case, space and parentheses) > Next > Finish.
2. Use SUBSTITUTE Function to Remove Special Characters from the Email Column:
   • Assuming email addresses have no special characters other than "@" and ".", use:

Adjust the formula as needed to remove other specific characters.

3. Concatenate Address, City, State, and Zip Code Columns into a Single Address Column:
   • Assuming columns are structured as Address, City, State, and Zip Code (e.g., D2, E2, F2, G2), use:

Adjust the references based on your actual column layout.

4. Use TRIM Function to Remove Leading and Trailing Spaces from the Customer Name Column:
   • Assuming Customer Name is in column B, use:

Drag the fill handle down to apply to all cells in the Customer Name column.

5. Create a Data Validation Dropdown List for Selecting Different Customer Names:
   • Go to Data > Data Validation > Allow: List.
   • In Source, enter the range of Customer Name values (e.g., B2).
   • Check the box for "In-cell dropdown" > OK.

Detailed Steps:

• Step 1: Split Phone Number Column
  • Select the Phone Number column.
  • Go to Data > Text to Columns > Delimited > Next.
  • Choose appropriate delimiters (space and parentheses) > Next > Finish.
• Step 2: Remove Special Characters from Email Column
  • Assuming the Email column is in column C, use a formula like:

Adjust to remove other special characters if needed.

• Step 3: Concatenate Address Components
  • Assuming Address is in column D, City in E, State in F, and Zip Code in G, use:Adjust the cell references based on your actual data layout.
• Step 4: Remove Leading and Trailing Spaces
  • Use the TRIM function to clean up leading and trailing spaces in the Customer Name column:

Drag the fill handle down to apply to all cells in the Customer Name column.

• Step 5: Create Data Validation Dropdown List
  • Select a cell where you want the dropdown list (e.g., H2).
  • Go to Data > Data Validation > Allow: List.
  • In Source, enter the range of Customer Name values (e.g., B2).
  • Check the box for "In-cell dropdown" > OK.