Return an Alpha based on number position in a word

[Don Anderson]

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

 

[Domenic]

Maybe...

C2, copied down:

=MID("J"&$C$1,MID(B2,1,1)+1,1)&MID("J"&$C$1,MID(B2,2,1)+1,1)&MID("J"&$C$1
,MID(B2,3,1)+1,1)&MID("J"&$C$1,MID(B2,4,1)+1,1)

Hope this helps!

 

[Ron Coderre]

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

 

[Ragdyer]

Just to add to Domenic's fine suggestion, if Column B is *true* XL dates,
formatted as 'ddmm', then you might try this:

=MID("J"&$C$1,MID(TEXT(A2,"ddmm"),1,1)+1,1)&MID("J"&$C$1,MID(TEXT(A2,"ddmm")
,2,1)+1,1)&MID("J"&$C$1,MID(TEXT(A2,"ddmm"),3,1)+1,1)&MID("J"&$C$1,MID(TEXT(
A2,"ddmm"),4,1)+1,1)

 

[Ron Coderre]

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

 

[Don Anderson]

Domenic,

Unless I'm missing something....
C2=CHHJ
C3=CHHJ
C4=CHHA
C5=CHHA

Not...
C2 = JAJD
C3 = JBJD
C4 = JCJD
C5 = JDJD

Thanks for your reply,
Don

 

[Domenic]

Sorry Don! I didn't realize that Column B contained true date values.
But I see RagDyer has modified the formula accordingly.

Thanks RagDyer!

Cheers!

 

[Ron Rosenfeld]

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

 

[Don Anderson]

Ragdyer,

That worked perfectly, since I did have B2:B366 formatted for a 'true' DDMM.

Thank you,
Don

 

[Don Anderson]

Domenic,

With Ragdyer's slight modification to your original formula, it worked
perfectly. I should have been more clear that it was truely xl date format.
As a test,I changed the ddmm to text and your solution worked as expected.

Thanks for your help
Don

 

[Ron Rosenfeld]

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

Seeing elsewhere that your values in column B are true dates, you just need to
modify the above formulas by replacing "0000" with "ddmm"

So:

(array entered):

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

OR (entered normally):

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


--ron

 

[Don Anderson]

Domenic, Ragdyer, Ron, Ron,

Thanks for all your terriffic suggestions!

Thanks,
Don