| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an Alpha based on number position in a word
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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! In article , "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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Domenic" wrote in message ... 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! In article , "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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
Ragdyer,
That worked perfectly, since I did have B2:B366 formatted for a 'true' DDMM. Thank you, Don "Ragdyer" wrote in message ... 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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Domenic" wrote in message ... 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! In article , "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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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" wrote in message ... 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! In article , "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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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! In article , "Don Anderson" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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 "Domenic" wrote in message ... 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! In article , "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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
On Sun, 02 Apr 2006 12:03:18 -0400, Ron Rosenfeld
wrote: 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 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return an Alpha based on number position in a word
Domenic, Ragdyer, Ron, Ron,
Thanks for all your terriffic suggestions! Thanks, Don "Don Anderson" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple rows of data based on criteria | Excel Worksheet Functions | |||
| Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
| Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
| Return number of current sheet | Excel Worksheet Functions | |||
| How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) | |||
Hybrid Mode
