Formula that removes chars in parenthesis regardless of length.

Please Login or Register  to view this content.

i am using this formula to flip first and last name, remove the comma, and also remove (NonEmp) (hence the -8 bolded in the second FIND function.

So if Jones, Jack R (NonEmp) were in A13, it would show like this Jack R Jones (NonEmp)
or Jones, John would show simply as John Jones

The problem is, I want to plan ahead and have it remove the Parenthesis and it's contents regardless of length. So if it is entered (Non Emp) it will still remove.

I tried this to no avail... Bolded is what changed.

Please Login or Register  to view this content.

any suggestions?

Can you give a sample of what a given cell looks like now, and how you want it to look when you're done?

-edit: thanks for the update

Orignal = Desired
1.Jones, Jack R (NonEmp) = Jack R Jones (NonEmp)
2.Jones, Jim (Non Emp) = Jim Jones (NonEmp)
3.Jones, John = John Jones

Right now "2." shows up as "Jim ( Jones (NonEmp)"

Originally Posted by djapigo

Here's a shorter version...

=SUBSTITUTE(RIGHT(A3,LEN(A3)-FIND(",",A3)-1)&" "&LEFT(A3,FIND(",",A3)-1)," (NonEmp)","")

Hmm...That would make "Jones, Jack R (Non Emp)" show as "Jack R (Non Emp) Jones"
I am striving to make it show as "Jack R Jones (NonEmp)"

Meaning, the original data could have (NonEmp) or (Non Emp) at the end, or it could just have Last Name, First Name MI.

So it either has Parenthesis around something at the end or nothing at all but the name.

Definitely appreciate the effort though!

Please Login or Register  to view this content.

Really, I think the best solution for my problem, is to have a function count the characters between "(" and ")" and add 2 to it.

just not sure what that would be.

then I can replace -FIND(",",A13)-8 with that function.


Edit
I am confused, because =LEN(A13)-FIND("(",A13)+1 returns 8 when it is used alone, if A13 contains "Jones, Kristina (NonEmp)"

It cuts off the name though.

So anything longer than the number of characters after "(" gets cut off.

Jones, Kristina (NonEmp) Becomes Kristin Jones (NonEmp)
but Jones, Kristina (Non Emp) becomes Kristina Jones (NonEmp)

Edit: I figured it out.

Please Login or Register  to view this content.

I needed to change "MID(A13,FIND(",",A13)+1" to "MID(A13,FIND(",",A13)+2" to account for the space after the comma.
Though some records might not have a space after the comma... Just the ones I am testing do.

so if a name is like this "Jones,James L(NonEmp)" It wouldn't display right..

I guess my new question is, is there a way to modify my formula to account for both the space after the comma and it not having a space, as ill chop off letters if I do not account for that.

Please Login or Register  to view this content.

FIND(",",A13)+2 Is the part I am worried about. "+2" assumes a space. +1 would assume no space.

SO maybe something like this

Please Login or Register  to view this content.

However, I messed that up somehow because it returns #VALUE!.

A little late to the party, but...
=IFERROR(MID(A1,FIND(" ",A1)+1,FIND(" (",A1)-FIND(" ",A1))&LEFT(A1,FIND(",",A1)-1)&" (NonEmp)",MID(A1,FIND(",",A1)+2,LEN(A1))&" "&LEFT(A1,FIND(",",A1)-1))

And this one takes care of the case when there is no space between the comma and the name:
=IFERROR(TRIM(MID(A1,FIND(",",A1)+1,FIND(" (",A1)-FIND(",",A1)))&" "&LEFT(A1,FIND(",",A1)-1)&" (NonEmp)",TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&" "&LEFT(A1,FIND(",",A1)-1))

Also noticed it handles cases like:
Jones Jr., Jack (NonEmp)

Probably easier for you to just do a find and replace of your input text for "(" and replace with " (". The above formula should handle the extra space if added.

This looks like it works:
=IFERROR(TRIM(MID(A1,FIND(",",A1)+1,FIND("(",A1)-FIND(",",A1)-1))&" "&LEFT(A1,FIND(",",A1)-1)&" (NonEmp)",TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&" "&LEFT(A1,FIND(",",A1)-1))