join random numbers of the alphabet in one cell

[jts2004]

I need to join random numbers in the alphabet based on cell ref #.

EX: A1=1, A2=7, A3=9
ALPHABET IS IN CELL B1

ANSWER= AGI

This is a simplified version of what I need to do. My "alphabet" is a 1500 character text string and my cell ref are a in ranges of 5 rows X 13 colomns.

 

[yee388]

Something like this?

Book1
	A	B	C	D
1	20
2	5	TEST
3	19
4	20
5
6	ABCDEFGHIJKLMNOPQRSTUVWXYZ
Sheet1

 

[cherria]

Well

=Mid(b1,A1,1) gives you the character from the alphabet in B1 in position A1

and =Mid(b1,A1,1)&Mid(b1,A2,1)

combines 2 together you just need to string this together another 63 times to get the whole string

or a user defined function to do the same might be

function cypher(mat as range, alph as string) as string
for each a in mat
cypher=cypher&mid(alph,a.value,1)
next
end function

then just call the function as =CYPHER(a2:M6,A1)

where A2:M6 has your matrix and A1 has the 1500 character alphabet string

 

[jts2004]

I thought of that but that would mean I would have to write a formula with 120 mid() strings together. Thanks anyway

 

[yee388]

Try using cherria's UDF. It'll build your MID() functions for you in one simple formula.

 

[jts2004]

do I just copy that code into a module? I have never created a function before

 

[yee388]

Yes, insert the code into a standard module, and when you use: "=CYPHER(range, alpha)", it will work just like a normal function.

 

[jts2004]

I'm getting a syntex error/compile error

 

[yee388]

Works for me... make sure it's in a standard module, not a Sheet or ThisWorkbook module:

Book1
	A	B	C	D
1	20	TEST
2	5	TEST
3	19
4	20
5
6	ABCDEFGHIJKLMNOPQRSTUVWXYZ
Sheet1

Function cypher(mat As Range, alph As String) As String
For Each a In mat
cypher = cypher & Mid(alph, a.Value, 1)
Next
End Function

 

[yee388]

Though it could be that your alpha string is too long...