replacing/altering certain parts of a string

[Robby87]

Excel/VBA gurus:

I have a very long column of strings that looks like this:

. A
1 one num 1.23 JUL27/09
2 three num 2.34 NOV8/10
3 five num 2.59 DEC10/10
4 seven num 3.90 JAN1/11

I want to alter the first word (one, three, five..etc), based on the following reference array (which is actually much longer):

. A B
1 one fish
2 three dog
3 five cat
4 seven horse


I also want to delete the middle word "num", and alter the date so it shows up as mm/dd/yy.

Could I do this in VBA/excel somehow?

 

[Andrew Poulsom]

First make a 2-column list like your reference array and name it Table using Insert|Name|Define (I used G1:H4). If your data is in A1:A4, in B1 enter:

=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),Table,2,FALSE)

in C1 enter:

=RIGHT(A1,LEN(A1)-FIND("!",SUBSTITUTE(A1," ","!",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

in D1 enter:

=SUBSTITUTE(MID(A1,FIND("num",A1)+LEN("num"),255),C1,"")

and in E1 enter:

=B1&D1&TEXT(SUBSTITUTE(RIGHT(C1,LEN(C1)-3),"/",LEFT(C1,3)),"mm/dd/yy")

Copy those four formulas down.

 

[Robby87]

Andrew, I'm really impressed. This does exactly what I wanted it to. Thanks so much.