Aligning text strings with master sequence

[tabecker]

Hi everyone,
I have text data that I need to line up with a sequence of letters in columns. I'm using Excel for Mac 2011, so it doesn't have the newest tools. I'm pretty sure that I need the functions IF, AND, MID, LEN and maybe INDIRECT. The problem is that I can't figure out what order to write the logic statements. I have uploaded a screen shot of an example. The text to be distributed is in A3. The desired result is highlighted C7:R7. The sequence to be matched is in Row 2, with corresponding numbers in Row 1. Column B has the starting number of the text that is to be aligned. Please note that this is small example. The sequence is actually over 500 letters long and the some of my text inserts are above 30 letters long.
I placed the corresponding formulas used (w/o the =) in cells A4:A6. I did succeed in using "AND" to achieve the empty space and "INDIRECT" to get a single letter in a cell, but not with putting it all together.
Thanks,
Tynan

 

[jtakw]

Hi,

Hope I understand your description correctly.
I don't use a Mac, but this formula should work with it.
Formula in C7 dragged across to R7

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2
3	DTICIGYN	8
4
5
6
7								D	T	I	C	I	G	Y	N
Sheet804

Cell Formulas
Range	Formula
C7:R7	C7	=IF(C1<$B3,"",MID($A3,COUNTA($B7:B7)-COUNTBLANK($B7:B7)+2,1))

 

[tabecker]

Hi,

Hope I understand your description correctly.
I don't use a Mac, but this formula should work with it.
Formula in C7 dragged across to R7


Thank you jtakw! It works perfectly. I'll have to look up and understand the COUNTA and COUNTBLANK functions. Thanks again!

 

[tabecker]

Thank you jtakw! It works perfectly. I'll have to look up and understand the COUNTA and COUNTBLANK functions. Thanks again!

 

[jtakw]

You're welcome, thanks for the feedback.