Right Formula

[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.

 

[Mike H]

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

 

[alish]

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.

 

[Bob Phillips]

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)

 

[Mike H]

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

 

[alish]

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.