Right Formula

  • Thread starter Thread starter alish
  • Start date Start date
A

alish

Hi ALL,
I need your help in below issue:
I have a column A with below text:

(RATIONAL) 4 - STT APPROVED PLUMBER
(additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR
(RATIONAL) 6 - FTT APPROVED ELECTICIAN
(RECOMMENDED) 6 - FTT APPROVED ELECTICIAN

Out of that column I want column B with the below results:
APPROVED PLUMBER
UNAPPROVED ELECTRICAL SUPERVISOR
APPROVED ELECTICIAN
APPROVED ELECTICIAN

What formula do I use to get the result in column Bt?
Thanks.
 
Hi,

This 'probably' works. I say probably because it looks for TT<space> and
extracts the characters after that so if this seat of characters is repeated
in the string it could fail. So put this in B1 and drag down

=MID(A1,FIND("TT ",A1,1)+3,LEN(A1))

Mike
 
Mike,
Thanks for your response. It did not work. It gave me #VALUE! error. And,
Yes, the "TT" will be repeated. Please let me know if you know any other
methods.

Regards.
 
It worked fine for me in your example data. Is the real data different?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi,

To have got a #Value! error means that the formula didnn't find the sequence
of characters TT<space> in A1 which is odd because that sequence appears in
every one of your posted examples.

With regards to other ideas they would mostly be variations on the same them
i.e. find a unique character or sequence of characters and use that to work
our where the data you want to extract are. For example your posted examples
all have a single "-" character and the data you want are 6 characters to the
right of that so this works for every posted example

=MID(A1,FIND("-",A1,1)+6,LEN(A1))

Likewise you could you the ) character of which there is only 1
=MID(A1,FIND(")",A1,1)+11,LEN(A1))

But as you will see this thime we are 11 characters from what you want so
the risk of error increases.

You could also consider text to columns

Mike
 
Mike,

The second one WORKED! THe first one worked but not in all cases where I
have two words in the breckets and in somce cases after the *TT words there
was one more word. I used the second formula but within the IF formula.
Normally the second word from the *TT is the same repeating word, and so it
was easied to use IF formula.

THank you ALL.

Alish.
 
Back
Top