Text Functions‎ > ‎

Extract Any Word

                                 Sample file:  ParseWords.xls

PROBLEM 1:
"I need to reformat a longer text string into a new format. The number of words is the same, but lengths are varying."

                                                          Example 1
  A
1 413 09/19/10 06:00 PM user111 [423]
2 Emma Smith Corporate Challenge - September   10 10 100.00% 5    
3 414 09/19/10 06:17 PM user2 [424]
4 Kate Jones Corporate Challenge - September   10 10 100.00% 5    
5 416 09/20/10 08:36 AM user3 [118]
6 Geoffrey Stevens Corporate Challenge - September   10 10 100.00% 5    
7 433 09/27/10 12:11 PM user4 [438]
8 Michael Walker Corporate Challenge - September   10 10 100.00% 5    

          Into:
  B C D E F G H I J K L
1 413 09/19/10 06:00 PM user111 [423] Emma Smith Corporate Challenge - September 10 10 100.00% 5
2 414 09/19/10 06:17 PM user2 [424] Kate Jones Corporate Challenge - September 10 10 100.00% 5
3 416 09/20/10 08:36 AM user3 [118] Geoffrey Stevens Corporate Challenge - September 10 10 100.00% 5
4 433 09/27/10 12:11 PM user4 [438] Michael Walker Corporate Challenge - September 10 10 100.00% 5

PROBLEM 2:
"I need to extract a specific word value from a longer text string."

                                             Example 2
 ABCD
1Dates FromTo
2From 10/1/2009   To  1/7/2010 10/1/20091/7/2010
3From 6/1/2010   To  1/7/2011 6/1/20101/7/2011
4From 9/1/2010 9/1/2010 
5From 4/3/2010 To 5/1/2011 4/3/20105/1/2011


SOLUTION:  A User-Defined Function (UDF) called WORD()
      The following UDF is installed in a regular code module:

CODE

Function WORD(Sentence As Range, ParamArray Args() As Variant) As String
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/11/2010
'Summary:   Pull out as many words from a text string as you want
'           Used as    =WORD(A1, 1, 2, 4, 7)    =WORD(A1, 5)
Dim MyArr As Variant
Dim Wrd As Long

MyArr = Split(Application.WorksheetFunction.Trim(Sentence.Text), " ")

    For Wrd = LBound(Args) To UBound(Args)
        WORD = WORD & " " & MyArr(Args(Wrd) - 1)
    Next Wrd
   
    WORD = Trim(WORD)
End Function


In the Example 2 above, it would be used in cell C2 as:
=WORD($A2, 2)

The first parameter is the cell to extract from.
The second parameter is the word number(s) required.

The UDF can be used to pull multiple words by simply adding more variables at the end. As seen in Example 1 above, the formula in H1 would be:
                              =WORD($A2, 3, 4, 5, 6)

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments