Extract Any Word

"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

A

B

C

D

1

Dates

From

To

2

From 10/1/2009 To 1/7/2010

10/1/2009

1/7/2010

3

From 6/1/2010 To 1/7/2011

6/1/2010

1/7/2011

4

From 9/1/2010

9/1/2010

5

From 4/3/2010 To 5/1/2011

4/3/2010

5/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 VariantDim Wrd As Long