• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trim/extract text - with a tricky "(" as obstacle

KFozzy

New Member
Hi All

I'm working on a long list of client records - all very old.
Historically, when staff recorded in this list they had to add a fee earner's initials after the client name. Now I need to cut these initials from the fields and move them to another column.
The initials are always put in (brackets) after the clients name, and are usually 3 character long: (ZZZ).

The extracting and moving the initials went very well (using =Right(Text,5) into new column) but now I need to delete these from the end of each record.

I've found a useful way in this post which I had great hopes for: http://chandoo.org/wp/2010/01/19/usernames-from-email-formulas/


So I went ahead applying the formula to my sheet... However, the tricky obstacle is that the character I need to use to mark the point of trimming (like the "@" in the example in the above post) is actually a (...
I cannot enter "(" into the formula - it just cries out for a closing bracket and giving me all kinds of grief.

Any ideas?

Thank you,
K.
 
If you are wanting the bracketed information:
=MID(A2,FIND("(",A2),999)

If you want the first part, not including bracketed information:
=LEFT(A2,FIND("(",A2)-1)
 
K

A bracket by itself is part of a pair of Brackets () and Excel expects the two in pairs
To search for it as Luke suggested you need to tell Excel that it is text and so enclose it in quote marks like "("
 
Thank you both.

that's exactly what I tried to start with. My formula looked like Luke's.
But if you try this Excel's response is "Your formula is missing a parenthesis... etc..." - that's why this is a tricky obstacle.
 
As Hui said, you need to include the quotation marks, so that XL knows to treat it as text, and not a formula construction.
 
Yes, I DID include the quotation marks, and the end result was the parenthesis error above...

However, I think I've just found the solution: as there is a space preceding each brackets, I amended the Find formula to from "(": quotation bracket quotation
to
" (" : quotation space bracket quotation

This did the magic :):p
 
Glad you got something to work. Would be curious to have you copy/paste the formula that was causing the error.
 
No problem - anything to help curious minds.
Here is the one that didn't work:
=LEFT(A2,FIND("(",A2)-1)

This did work:
=LEFT(A2,FIND(" (",A2)-1)

(the difference is miniscule - there is a space before the ( within the "" marks
 
Strange. Your first formula is working fine on my end. Guess we slipped into the Twilight zone for a bit... :p
 
Back for more....

I wonder, how would this work on the reverse - can I use it with RIGHT to return the characters from the end of a text string from and including (?

I did try to use basic logic to "mirror" the formula to the right, but somehow I added up with results chopping off random number of characters from the beginning of the text and returning everything else.
This is how my "random stabbings at logic looked: =RIGHT(A2,FIND("(",A2))
(I want to include the opening bracket in the results too, so I omitted -1 from the formula, of course.)

I reasoned that if it can find the specific point to return everything BEFORE this point, then it MUST be able to do the same and return everything to the right of the same point....

Is there a solution?

Thank you,
K.
 
You could, but need to subtract. RIGHT is looking for how many characters from the left to extract. But, FIND is telling you how many characters from the left the string is. So, if we had
abcdefg
=RIGHT(A2,FIND("c",A2))
would give us "efg", because "c" is the 3rd character, and "efg" are last 3 characters.

To correct this, take the difference from length of text to the found character
=RIGHT(A2,LEN(A2)-FIND("c",A2))
Now this will do 7 - 3 = 4, and give us "defg"
 
Thank you both - both solutions worked like magic! Saved my archivist hours of "manual labour"!

Krisztina
 
Back
Top