Results 1 to 3 of 3

Thread: Splitting and Replacing text

  1. #1

    Splitting and Replacing text

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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*

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    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.

  3. #3
    Ahhh great thank you a ton!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts