Return an Alpha based on number position in a word

  • Thread starter Thread starter Don Anderson
  • Start date Start date
D

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
 
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!
 
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
 
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)
 
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
 
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
 
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!
 
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
Click to expand...

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
 
Ragdyer,

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

Thank you,
Don
 
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
 
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
Click to expand...

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
 
Back
Top