+ Reply to Thread
Results 1 to 6 of 6

Split name and role, get rid of parentheses ex: Last, First (Role)

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Split name and role, get rid of parentheses ex: Last, First (Role)

    I have a field with this convention:

    Last, First (Role)

    I want it split into two fields - Name and Role - and get rid of the parentheses at the same time. Any quick way to do this? I'd like to split it based on the "space open-parentheses" rather than the "second space from the left" because of some data inconsistency. Also, the role can be more than one word so I can't use first space from the right.

    I will handle splitting the name in a different operation because there are a few records with more than one comma which is screwy.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Split name and role, get rid of parentheses ex: Last, First (Role)

    You could use the Text to Column feature with the delimited option based on "(". Then repeat the Text to Column function on your new column, just change it to ")", which will delete the last parenthesis.

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Split name and role, get rid of parentheses ex: Last, First (Role)

    Last name:

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

    First Name...maybe
    =TRIM(MID(A1,FIND(",",A1)+1,FIND("(",A1,FIND(",",A1)+1)-FIND(",",A1)-2))

    Role...maybe

    =SUBSTITUTE(MID(A1,FIND("(",A1)+1,66),")","")

    These types of formulas always annoy me lol.


    Alternative would be to use Text to Column, use the Delimited option and select SPACE as the critera. Then just find and replace all the commas and ()'s with a "".
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  4. #4
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Split name and role, get rid of parentheses ex: Last, First (Role)

    That's what I will do if I cannot figure out a function. Since I am going to end up with two new columns I suppose Text to Columns might be as fast as any function.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split name and role, get rid of parentheses ex: Last, First (Role)

    in B1
    =LEFT(A1,FIND("(",A1)-2)

    in C1
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,"")," (",""),")","")

    Row\Col
    A
    B
    C
    1
    Last, First (Role) Last, First Role
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Split name and role, get rid of parentheses ex: Last, First (Role)

    Quote Originally Posted by Speshul View Post
    Last name:

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

    First Name...maybe
    =TRIM(MID(A1,FIND(",",A1)+1,FIND("(",A1,FIND(",",A1)+1)-FIND(",",A1)-2))

    Role...maybe

    =SUBSTITUTE(MID(A1,FIND("(",A1)+1,66),")","")

    These types of formulas always annoy me lol.
    That stuff does the trick!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding a new number row but clearing contents of new role
    By Armored Wing in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-11-2014, 11:18 PM
  2. Need formula based on role change month
    By sekharyadav in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-28-2013, 08:28 AM
  3. Role Based Security in Excel
    By parag kale in forum Excel General
    Replies: 2
    Last Post: 05-24-2007, 04:26 AM
  4. [SOLVED] how can i make grafs for role call?
    By people in forum Excel General
    Replies: 0
    Last Post: 10-08-2005, 11:05 PM
  5. How to make excell automatically select every other role for shadi
    By How to select every other role? in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-11-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1