On Sun, 2 Apr 2006 09:27:31 -0500, "Don Anderson"
wrote:

I would like to return 4 letters in a word based on their number position in
the word.
In B2:B366 I have DDMM and in C1 I have a 10 letter word ABCDEFGHIJ, where A
= 1 and J = 10.
In C2:C366 I would like to return the 4 letters from C1 that correspond to
the numbers in B2:B366.
Can I do this with a formula? If so, what would this formula look like?

B1 = DDMM
B2 = 0104
B3 = 0204
B4 = 0304
B5 = .....

C1 = ABCDEFGHIJ
C2 = JAJD
C3 = JBJD
C4 = JCJD
C5 = .....

Thx,
Don


If your string in C1 is always going to be ABCDEFGHIJ, then you could use the
**array-entered** (enter by holding down <ctrl<shift while hitting <enter --
XL will place braces {...} around the formula) formula:

=MCONCAT(CHAR(MID(TEXT(B2,"0000"),{1,2,3,4},1)
+IF(--MID(TEXT(B2,"0000"),{1,2,3,4},1)=0,74,64)))

MCONCAT is a function available if you download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr/forums and it can be easily
distributed with the workbook.

OR, if you don't want to download that add-in, and if your string in C1 may
change, then:

=MID(RIGHT($C$1,1)&LEFT($C$1,9),MID(TEXT(B2,"0000" ),1,1)+1,1)&
MID(RIGHT($C$1,1)&LEFT($C$1,9),MID(TEXT(B2,"0000") ,2,1)+1,1)&
MID(RIGHT($C$1,1)&LEFT($C$1,9),MID(TEXT(B2,"0000") ,3,1)+1,1)&
MID(RIGHT($C$1,1)&LEFT($C$1,9),MID(TEXT(B2,"0000") ,4,1)+1,1)


--ron