Prev: multiple sumproducts and now formula is too long
Next: How do you apply a print area to an entire workbook
From: Mike H on 14 Apr 2010 07:11
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?
> >
> >
> > .
> >
From: John Smith on 14 Apr 2010 09:28

"John Smith" <jbloggs(a)nospam.net> wrote in message
news:a56dnS1xN90EFVjWnZ2dnUVZ8hSdnZ2d(a)giganews.com...
> 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


From: Ron Rosenfeld on 14 Apr 2010 12:58
On Wed, 14 Apr 2010 10:19:12 +0100, "John Smith" <jbloggs(a)nospam.net> 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?
>

To assign numbers based on position in the alphabet means that you are only
assigning values to [A-Z]. No numbers, spaces, other characters.

To do that, a simple UDF, which you can modify depending on your ultimate
requirements.

==================================
Option Explicit
Function LtrToNum(s As String) As String
Dim i As Long
Dim temp
For i = 1 To Len(s)
temp = Asc(UCase(Mid(s, i, 1))) - 64
Select Case temp
Case 1 To 26
LtrToNum = LtrToNum & Format(temp, "00")
'no other characters in string are defined
Case Else
LtrToNum = LtrToNum & "??"
End Select
Next i
End Function
=================================

To limit the length of the string is another issue. Do you just want to be
able to tell that two strings are not identical? Some variation on an MD5 Hash
algorithm is probably what you are looking for.

If you need to reconstruct the original string from the number, you'll need
something different, especially considering Excel's limits of precision is 15
decimal digits, and there are going to be various rounding errors which will
need to be dealt with.
--ron
First  |  Prev  | 
Pages: 1 2
Prev: multiple sumproducts and now formula is too long
Next: How do you apply a print area to an entire workbook