RE: Return an Alpha based on number position in a word

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Actually, if the "J" is moved to the beginning of the string in C1, the
formula can shrink even more, because the double minus signs are not
necessary:

Instead of (112 characters):
C2:
=MID($C$1,--MID(B2,1,1)+1,1)&MID($C$1,--MID(B2,2,1)+1,1)&MID($C$1,--MID(B2,3,1)+1,1)&MID($C$1,--MID(B2,4,1)+1,1)

it could be this (104 characters):
C2:
=MID($C$1,MID(B2,1,1)+1,1)&MID($C$1,MID(B2,2,1)+1,1)&MID($C$1,MID(B2,3,1)+1,1)&MID($C$1,MID(B2,4,1)+1,1)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Maybe something like this:

C2:
=MID($C$1,IF(--MID(B2,1,1)>0,MID(B2,1,1),10),1)&MID($C$1,IF(--MID(B2,2,1)>0,MID(B2,2,1),10),1)&MID($C$1,IF(--MID(B2,3,1)>0,MID(B2,3,1),10),1)&MID($C$1,IF(--MID(B2,4,1)>0,MID(B2,4,1),10),1)

Note, though....if you move the "J" in C1 the beginning:
C1: JABCDEFGHI

Then C2 reduces to this:

C2:
=MID($C$1,--MID(B2,1,1)+1,1)&MID($C$1,--MID(B2,2,1)+1,1)&MID($C$1,--MID(B2,3,1)+1,1)&MID($C$1,--MID(B2,4,1)+1,1)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"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



.

Relevant Pages

  • Re: Float to String
    ... string contains three characters, blank, plus, and minus. ... Subject: Float to String ... > Monty Python's the Meaning of Life ...
    (comp.lang.ada)
  • Re: Float to String
    ... David C. Hoos, Sr. ... > string contains three characters, blank, plus, and minus. ...
    (comp.lang.ada)
  • Re: Why re.match()?
    ... You want to duplicate the whole lot, minus two ... characters. ... pattern to some prefix of your 4GB string? ... To have a special-case ...
    (comp.lang.python)
  • Re: breaking hyphen?
    ... >should not be allowed if it would leave a string of 3 or fewer ... especially if there are not many characters per line. ... Maybe allow break before hyphen-minus if it has digits on both sides? ... hyphen always allows break and minus never does. ...
    (comp.infosystems.www.authoring.html)
  • Re: How to convert Infix notation to postfix notation
    ... If this is for an error message, why isn't it using stderr for its output? ... array of 15 characters, and you call this function with the limit 15 on ... Making sure that the only string I allocate and append to, ... because mulFactor in all versions must needs incorporate the functions ...
    (comp.lang.c)