Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
To convert a text value to a number using a defined rule in Excel, say a
macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
John,
I thibk this 'idea' is going to be a minefield of potential errors but here's a UDF that works for you posted example. Function NumLtr(str As String) As String s = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y ,z" v = Split(s, ",") For x = 1 To Len(str) Step 2 letter = CLng(Mid(str, x, 2)) NumLtr = NumLtr + v(CLng(Mid(str, x, 2)) - 1) Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "John Smith" wrote: To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
With the word instructor in A1, this formula:
=TEXT(CODE(UPPER(A1))-64,"00") will return 09 for the first character in that word. You can use MID to select different letters from the word. However, there is not a multi-concatenation function in Excel to join those two-digit strings together, so you would need a user-defined function to do what you want. Presumably you would not want to include spaces or other punctuation symbols? You can always wrap LEFT around the function to limit the length of the returned string. Hope this helps. Pete On Apr 14, 10:19*am, "John Smith" wrote: To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
John,
This line isn't required, I used it for debugging and forgot to delete it letter = CLng(Mid(str, x, 2)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: John, I thibk this 'idea' is going to be a minefield of potential errors but here's a UDF that works for you posted example. Function NumLtr(str As String) As String s = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y ,z" v = Split(s, ",") For x = 1 To Len(str) Step 2 letter = CLng(Mid(str, x, 2)) NumLtr = NumLtr + v(CLng(Mid(str, x, 2)) - 1) Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "John Smith" wrote: To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
Here's a bit of a refinement. there's no alphabetic number for space so the
code now will 'interpret' the number 32 as a space so the string 16203209141920182103201518 would convert to "pt instructor" Function NumLtr(str As String) As String Dim x As Long v = Split("a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v ,w,x,y,z", ",") For x = 1 To Len(str) Step 2 If CLng(Mid(str, x, 2)) = 32 Then NumLtr = NumLtr + " " Else NumLtr = NumLtr + v(CLng(Mid(str, x, 2)) - 1) End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "John Smith" wrote: To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
Hmmm,
I completely misread the question. it's the other way around. This converts a string to a number sequence and uses 32 as a space Function LtrNum(str As String) As String Dim x As Long For x = 1 To Len(str) r = Asc(LCase(Mid(str, x, 1))) - 96 If r = -64 Then LtrNum = LtrNum & "32" Else If Len(r) = 1 Then LtrNum = LtrNum & "0" & r Else LtrNum = LtrNum & r End If End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Here's a bit of a refinement. there's no alphabetic number for space so the code now will 'interpret' the number 32 as a space so the string 16203209141920182103201518 would convert to "pt instructor" Function NumLtr(str As String) As String Dim x As Long v = Split("a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v ,w,x,y,z", ",") For x = 1 To Len(str) Step 2 If CLng(Mid(str, x, 2)) = 32 Then NumLtr = NumLtr + " " Else NumLtr = NumLtr + v(CLng(Mid(str, x, 2)) - 1) End If Next End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "John Smith" wrote: To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible...
"John Smith" wrote in message ... To convert a text value to a number using a defined rule in Excel, say a macro or some other formula? Take for example, say the value "Instructor" mapping to 09141920182103201518 with each letter corresponding to its position in the alphabet represented by a two digit number. However, the longer the word, the longer the numerical string, so I would like to be able to limit this in some way. I do not yet know which words or phrases will be entered, but is there some hash function available that will keep the numeric strings relatively short? Well, I think I have found another method, that will assign numeric values to each letter and then to SUM the numbers together to keep the string short. I don't think there will be much of a possibility of the numbers being duplicated as the words are all greater than 9 characters, although this changes as the word list gets longer A solution I found that works for pure text was to use: =SUM(1*CHOOSE(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1))-96, 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 ,21,22,23,24,25,26)) where the numbers 1-26 represent values for the letters a-z. However, it falls down when the word is a phrase separated by a space or a period (.) Another option I was looking at was having a defined list of words (~ 200) in another worksheet with a numeric value associated with each one, then when that word is entered in cell A2 of the first worksheet, then the associated numeric value is looked up from the second worksheet and automatically appears in cell B2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|