Splitting and Replacing text

Adrene

New member
Joined
Jul 3, 2015
Messages
2
Reaction score
0
Points
0
Hi There,

I need some help please.

I have a data set containing 65536 lines with details such as company name, names & surnames, email addresses etc. a lot of the line items contain duplicate details of the name & surname and email address. In order to remove these duplicates I have created a pivot table making it 13000 lines. In this pivot table the name & surname field appears in column B and the email address in column C.

I have two questions relating to the pivot table:
  1. How can I split the "Name & Surname" column to show name and surname separately in two different columns?
  2. Some of the email addresses contain "*40" instead of the "@" sign, how can I fix this in the data set without manually fixing each one. (E.G. Joe.Soap*40gmail.com)
I assume I would need to copy and paste columns B&C to a new worksheet and remove the pivot table functionality before I can action any formulas on the "new data set"?

I need an idiot proof step-by-step process on how to go about this please?

Thank you in advance!
Adrene
 
You will need to add some columns to the database to separate the Name & Surname.

Assuming the Name and Surname are in column A, starting at A2, and the name is separated by a space, then in a new column use formula to get first name:

=TRIM(LEFT(A2, FIND(" ",A2)-1))

copied down.

In the next column to get Surname

=TRIM(MID(A2,FIND(' ',A2)+1,255))

Copied down.

Make sure these columns have titles in top row for the Pivot Table.

For the email address, select the column that has the addresses. Then hit CTRL+H (or go to Home|Find & Select|Replace). Enter ~*40 (notice the ~ symbol in front to override the wildcard *) in the Find What field.
In the Replace With field simply enter a @

Now replace the Name & Surname column in your Pivot table with the 2 new separate columns and refresh the data.
 
Back
Top