Convert Text to number

mdd16

Board Regular
Joined
Jan 11, 2011
Messages
84
Office Version
  1. 365
Platform
  1. Windows
I want to convert a text to number.

Say if a cell contains "ABCD PRIVATE LIMITED"

I want get the first word converted to a predictable number.

Can someone help.

Mohan Dhingra
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
what type of predictable number you need there?
 
Upvote 0
Thanks for your quick response.

I want to restrict use of a file to registered users only, who will provide me with their intended user name and I will provide them with a password.

Based on the first word of the given user name, I want to generate a number which I will pass back to the user for providing access to the file.

One of the cells in the workbook will calculate password based on username. When the user opens the workbook, an input box will appear which will ask for username and password. Entered value of password will be matched with the calculated value in the workbook and if it matches, then access is provided otherwise application.quit

So ideally if say the first word in username is ABCD, then let say we convert it to 1234.

efgh becomes 5678 etc.

Looking forward to hearing from you.

thanks again for help

Mohan
 
Upvote 0
“ABCD” will not convert to numbers. They must be numbers entered as text.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
“1234 PRIVATE LIMITED”
<o:p></o:p>
Excel Workbook
AB
11234 PRIVATE LIMITED1234
Sheet1
Excel 2000
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND(" ",A1)-1)*1

<o:p></o:p>
The only way I can think of to convert ABCD to a number is split the word into letteres and use each letters ASCII number to string into a representative number.

Are you really asking to change a word into a number?
 
Upvote 0
Excel Workbook
AB
1ABCD PRIVATE LIMITED1234
Sheet1

Excel 2000


Rich (BB code):
Rich (BB code):
Rich (BB code):
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Option Explicit<o:p></o:p>
<o:p></o:p>
Sub ConvertWord()<o:p></o:p>
Dim Word As String<o:p></o:p>
Dim i As Integer<o:p></o:p>
Dim WordNumber As String<o:p></o:p>
Dim Number As Long<o:p></o:p>
<o:p></o:p>
Word = Left(Range("A1"), InStr(Range("A1"), " ") - 1)<o:p></o:p>
Word = LCase(Word)<o:p></o:p>
For i = 1 To Len(Word)<o:p></o:p>
WordNumber = WordNumber & Asc(Mid(Word, i, 1)) - 96<o:p></o:p>
Next i<o:p></o:p>
Number = WordNumber * 1<o:p></o:p>
Range("B1") = Number<o:p></o:p>
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top