Convert text into certain numbers

oOpsy

New Member
Joined
Nov 27, 2005
Messages
21
Hi there,

I'm currently working on a project .I will like to know if it is possibe to actually convert text into certain numbers? An example will be if user select ' system 1', upon clicking the macro button to update sheet2 with the information, it will display '101' in sheet2. Meaning i can define the text to convert into certain numerals.

like system 1 (in sheet 1) will become 200(set by me) in sheet 2 using vba.

Thanks in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi - welcome to the board!

Yes - things like that are possible, though to say anything more specific, you'll need to give us a more detailed example.
 

oOpsy

New Member
Joined
Nov 27, 2005
Messages
21
Hi~~

Hope i can explain this properly:

settings.xls is meant for users to select their options. etc column 1 is to select either voltage, current, frequency or resistance. Upon finishing the selection, users should click on the macro button embedded on the sheet to update another wksheet, config.xls.

However instead of display 'voltage' in congfig.xls, i will like to display the assigned code (let say 101) to it. Need help on how to tell excel that voltage = 101, current = 102 etc.


Thanks a million!
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you don't need a macro to do that sort of thing. I'd do it like this:

1) Set up a source table that includes the allowable entries (voltage etc) in one column and their associated valies (101 etc)

2) Set up the data entry using data validation (check it out in the help files) to ensure only legitimate values are entered

3) use a vlookup() formula to return the codes for the entries - again, review excel help file for vlookup(), and search the board for examples of its use, including:

http://www.mrexcel.com/board2/viewtopic.php?t=62709

...post back if yo can't get it sorted - but this time include the details of what ranges your data occupies etc.

an example follows:
Book1
ABCDEF
1SourceReturnCategoryValue
2Voltage101Voltage101
3Current102Current102
4Current102Frequency103
5Frequency103Resistance104
6Resistance104
7Voltage101
8
Sheet1


formula is:

=VLOOKUP(A2,$D$2:$E$5,2,0)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,028
Messages
5,569,741
Members
412,289
Latest member
Kingchaos64
Top