retrieving one word

[migdad]

In Excel I would like to know how can I get out one word from inside field by using the condition of the first letters in that word?

Example
Give me the word starting by RAJ from the below,
FERNAS ,NAMAS (RAJHIA1L078002C5-2863) 21ST OLAYA

Thanks

[martindwilson]

not sure exactly what bit you want, this can be adjusted to suit , with text in a1 put

Please Login or Register  to view this content.

in c1
type raj (upper or lower in b1)

[shg]

You could also use SEARCH, which is not case sensitive:

=MID(A1, SEARCH(B1, A1), SEARCH(")", MID(A1, SEARCH(B1, A1), 255) ) - 1)

[migdad]

martindwilson and shg
I appreciate your support, but If the word at the beginning of the sentence or at the end (not in the middle)
What do I do??

Thanks

[shg]

Would you provide examples, please?

[migdad]

Dear shg,
Thanks for you support
Example #1
Give me the word starting by RAJ from the below,
RAJHIA1L078002C5-2863 21ST OLAYA FERNAS ,NAMAS

Example #2
Give me the word starting by RAJ from the below,
OLAYA FERNAS ,NAMAS RAJHIA1L078002C5

[NBVC]

Try:

=MID(A1,SEARCH(B1,A1),SEARCH(" ",MID(A1&" ",SEARCH(B1,A1),255)))

Where A1 contains original string and B1 contains string to search for.

[migdad]

Try:

=MID(A1,SEARCH(B1,A1),SEARCH(" ",MID(A1&" ",SEARCH(B1,A1),255)))

Where A1 contains original string and B1 contains string to search for.

Thanks NBVC
It is working with the word in the beginning of the sentence, but what about the word in the end of sentence??

[NBVC]

It should also work with word at end of sentence...

I tested it with your example#2 and got the expected result.

Have you tried it? What error do you get?

[oldchippy]

Hi,

Just adding to NBVC example and looking at your very first example where its enclosed in brackets, you may need something like this

Please Login or Register  to view this content.

[migdad]

It should also work with word at end of sentence...

I tested it with your example#2 and got the expected result.

Have you tried it? What error do you get?

Dear NBVC,
sorry for confusing you
It is working for both examples

Thaaaaaaaaaaaaaanks

[migdad]

Hi,

Just adding to NBVC example and looking at your very first example where its enclosed in brackets, you may need something like this

Please Login or Register  to view this content.

Thanks a lot

[oldchippy]

Sorry error in my last formula, but this will work with both brackets and non-brackets

Please Login or Register  to view this content.