Prev: multiple sumproducts and now formula is too long
Next: How do you apply a print area to an entire workbook
From: John Smith on 14 Apr 2010 05:19
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: Mike H on 14 Apr 2010 06:15
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?
>
>
> .
>
From: Pete_UK on 14 Apr 2010 06:16
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" <jblo...(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?

From: Mike H on 14 Apr 2010 06:17
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?
> >
> >
> > .
> >
From: Mike H on 14 Apr 2010 06:29
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?
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: multiple sumproducts and now formula is too long
Next: How do you apply a print area to an entire workbook