Excel Functions

[KishorKK]

Hi Friends,

I would like to know the excel text funtions relted to Mid Function

For suppose names like "Bharti Vidya Devi"
i need "Vidya" mid letters every time i can't count if data is in hundreds so, any way to extract the mid letters.

 

[David Evans]

Have you tried using the Text to Columns feature on the Data Tab?

 

[Chihiro]

Try something like...
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

 

[bosco_yip]

Maybe.......

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

Regards

 

[sajjjid]

Try something like...
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

Works Fine
How to get bharti and devi. thanks

 

[Chihiro]

For a 3 word string in a cell (i.e. words separated by space).

1st word:
=LEFT(A1,FIND(" ",A1)-1)

Middle word (only works for 2nd word)
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

Last word
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[sajjjid]

For a 3 word string in a cell (i.e. words separated by space).

1st word:
=LEFT(A1,FIND(" ",A1)-1)

Middle word (only works for 2nd word)
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

Last word
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

Thanks very much.

 

[shrivallabha]

Some links which will be useful if you want to explore and understand:
http://forum.chandoo.org/threads/pick-extract-a-word-by-position-from-a-cell.7441/

http://www.excelfox.com/forum/showt...ext-String?s=f24511ec64311703a506d4d2f7b74ab7