How do I remove partial text with a cell?

Question

I have downloaded my contacts from my CRM where contact names are condensed in each cell with a date acquired, first name and last name, i.e. "150505 Bobby Jones". 1) Is there a way to remove the date from the names? OR 2) Is there a way to divide the one cell into three, i.e. date, first, and last?

Answer 1

Hello,

A few ways to go about this -

Option 1, you can use Text to Column feature found at the Data tab. Choose Delimited and then click next, then check space, and finish.

This will then turn the data into three columns:

150505 | Bobby | Jones

You can then use =concatenate( ) function to merge Bobby and Jones

Option 2, you can use this function to just get rid of the dates directly (assuming the data lives in A2)

=TRIM(MID(A2,FIND(" ",A2)+1,LEN(A2)))

Answer 2

To remove the date from the names or to split the cell into three separate columns (date, first name, and last name), you can use Power Query in Excel. Here’s how you can achieve both tasks:

1. Removing the Date: You can use the Text.Middle function to extract the first name and last name while ignoring the date. For example, if your data is in a column named ContactInfo, you can add a new column that extracts the text starting from the 7th character:

      Table.AddColumn(Source, "Name", each Text.Middle([ContactInfo], 7), type text)
   

   This will give you just the names without the date.
2. Splitting the Cell: To split the cell into three parts, you can use the Split Column feature in Power Query. Here’s how:
   • Select the column containing the contact information.
   • Use the Split Column option and choose to split by delimiter (space) or by number of characters. If you know the format is consistent, you can split by the first 6 characters for the date and then split the remaining text by space to separate the first and last names.
   • After splitting, you can rename the new columns accordingly.

For more detailed steps on splitting columns in Power Query, you can refer to the following examples:

• Split by delimiter: Split columns by delimiter into rows
• Split by number of characters: Split columns by number of characters into columns

Using these methods, you can effectively manage and format your contact data as needed.

Answer 3

Thank you. The Split Columns solution worked wonderfully.