Convert strings into numbers

[iancshaw]

Hi,

I need to convert a spreadsheet of database results into numbers. Is there any way that Excel can convert each unique value in a column into a unique number for importing into a statistical software package. For example a column on 'marital status' would convert as follows:

Married = 0
Single = 1
Widowed = 2
etc

I think it might be able to be done in conjuction with filtering but I can't work it out. If it is possible, I would like to have it performed with a macro so that it can be provided to other users of the database.

Any help would be greatly appreciated,
Ian

[zzdave]

Are you asking how to conver convert the word 'married' to a 1? You can simply do a nested if() function for this. e.g. if(b2="married",1,if(b2="single",2,3))
or something like.
Hope this helps

[Ian Mac]

On 2002-05-22 00:04, iancshaw wrote:
Hi,

I need to convert a spreadsheet of database results into numbers. Is there any way that Excel can convert each unique value in a column into a unique number for importing into a statistical software package. For example a column on 'marital status' would convert as follows:

Married = 0
Single = 1
Widowed = 2
etc

I think it might be able to be done in conjuction with filtering but I can't work it out. If it is possible, I would like to have it performed with a macro so that it can be provided to other users of the database.

Any help would be greatly appreciated,
Ian

Set up your differant column parts into tables and use VLOOKUP()

E.g.

Name a sheet Definitions (or whatever)
then set up diffent table as to your requirements, like:

Microsoft Excel - PO Number_052102-04.xls ___Running: xl97 : OS = Windows (32-bit) NT 4.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = Status
	A	B	C	D
1	Status	Code
2	Married	0
3	Single	1
4	Separated	2
5	Widowed	3
6	Going out with some girl/boy	4
7	Etc.	5
Definitions

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Then you can name the ranges

Highlight the Area with your table, goto
Insert>Names>Define from the menu bar

Call your table something you'll remember, say Status.

now on a second sheet use
=VLOOKUP(A1,Status,2,0)

like this:

Microsoft Excel - PO Number_052102-04.xls ___Running: xl97 : OS = Windows (32-bit) NT 4.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = Married
	A	B	C	D
1	Married	:alert('=VLOOKUP(A1,Status,2,0)')>0
2	Single	:alert('=VLOOKUP(A2,Status,2,0)')>1
3	Married	:alert('=VLOOKUP(A3,Status,2,0)')>0
4	Widowed	:alert('=VLOOKUP(A4,Status,2,0)')>3
5	Separated	:alert('=VLOOKUP(A5,Status,2,0)')>2
6	Going out with some girl/boy	:alert('=VLOOKUP(A6,Status,2,0)')>4
7	Married	:alert('=VLOOKUP(A7,Status,2,0)')>0
8	Single	:alert('=VLOOKUP(A8,Status,2,0)')>1
9	Married	:alert('=VLOOKUP(A9,Status,2,0)')>0
10	Etc.	:alert('=VLOOKUP(A10,Status,2,0)')>5
11	Married	:alert('=VLOOKUP(A11,Status,2,0)')>0
MySheet

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Click on the underlined number for the formula
Hope this helps
Any question ask

[iancshaw]

Thanks for the help.

I don't suppose there is any way it can be done a little more intuitively so that the use of the definition sheet is not required. This would just allow new columns to be added at a later stage without having to change the definition.

Any more suggestions would be great,
Ian