Subscribe
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
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 FunctionExample 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
