Return an Alpha based on number position in a word

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
 
D

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!
 
G

Guest

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
 
R

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)
 
G

Guest

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
 
D

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
 
D

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!
 
R

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
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
 
D

Don Anderson

Ragdyer,

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

Thank you,
Don
 
D

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
 
R

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
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
 
D

Don Anderson

Domenic, Ragdyer, Ron, Ron,

Thanks for all your terriffic suggestions!

Thanks,
Don
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search an alphanumeric in a column and return true/false 9
using sumif & countif to sort multiple cells 3
Is there a formula that can do this? 3
return values in the table with multiple conditions 3
change operating cells dynamically according to the value of a fixed cell 2
Allocate Value Across Monthly Periods 1
return a value based on a range 3
Change formulas in a column based on a selection. 3

Top