In the example below, how do I write a formula that will replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the desired results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

Not sure what "relative reference" has to do with this problem that looks
like a homework assignment.
Try this in C1 and then copy it to the other cells
=LEFT(C1,FIND(" ",C1))&INDIRECT("B"&INT(ROW()/9)+1)&"
"&RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",2)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"gcotterl" wrote in message
...
In the example below, how do I write a formula that will replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the desired results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

gcotterl wrote:
In the example below, how do I write a formula that will replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the desired results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

Hi,

Just an small improvement so that errors are not seen...

=IF(ISBLANK(C1),"",LEFT(C1,FIND(" ",C1))&INDIRECT("B"&INT(ROW()/9)+1)
&" "&RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",2))))

Ken Johnson

Hi Bernard,

"relative reference"

I was looking for an expression that would "refer":

B1 to C1
B2 to C10
B3 to C19
B4 to C28
B5 to C37

Gary




"Bernard Liengme" wrote:

Not sure what "relative reference" has to do with this problem that looks
like a homework assignment.
Try this in C1 and then copy it to the other cells
=LEFT(C1,FIND(" ",C1))&INDIRECT("B"&INT(ROW()/9)+1)&"
"&RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",2)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"gcotterl" wrote in message
...
In the example below, how do I write a formula that will replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the desired results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

gcotterl wrote:
Hi Bernard,

"relative reference"

I was looking for an expression that would "refer":

B1 to C1
B2 to C10
B3 to C19
B4 to C28
B5 to C37

Gary


Hi Gary,

I think Bernard meant to say "Try this in D1 etc" not "Try this in C1
etc"

Ken Johnson