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

Pick/Extract a word by position from a cell

If you to extract/pick a word by its postion from a cell .
For example

1) Input: Procter & Gamble

Output(Pick 3rd word): Gamble

2) Input: Procter,&,Gamble

Output(Pick 1st word): Procter

Try this udf :
Code:
Function pick_word(str1 As String, spl As String, positon As Integer)
Dim arr1
arr1 = Split(str1, spl)
If positon > UBound(arr1) + 1 Then
pick_word = "Total No of words in a cell = " & UBound(arr1) + 1  & " Please make sure position entered is less than total no of words in a cell "
Else
pick_word = arr1(positon - 1)
End If
End Function

Example 1


In cell A1 We have: Procter & Gamble

And we want to pick 3rd word. In cell B1 type : = pick_word(A1," ",3)


pick_word(A1," ",3)

A1 - Input Cell

" " - Space is used as seperator in Input cell

3- Postion of word you want to pick


Example 2


In cell A2 We have: Procter,Gamble

And we want to pick 1st word. In cell B2 type : =pick_word(A1,",",2)


pick_word(A1,",",2)

A2 - Input Cell

"," - Comma is used as seperator in Input cell

2- Postion of word you want to pick
 
Last edited by a moderator:
Back
Top