I am working with MS Excel 2003 and want to enter data numerically, but have it appear as text?

Question

For instance, I want to enter the value "1" and have "male" appear and enter "2" and have female appear in the spreadsheet. I know how to do this in SPSS, but want to do it in Excel. Any help appreciated.

Answer 1

It seems that you want to immediately replace the '1' or '2' just entered into a cell with the text 'Male' or 'Female' in the SAME cell.

If so, here is how you can do that.

Open your workbook.

Copy this macro to the clipboard.

Sub Convert_to_MF()

Dim rng As Range

Set rng = Range(ActiveCell.Offset(0, -1).Address & _

":" & ActiveCell.Offset(-1, 0).Address)

For Each cell In rng

cell.Select

If Len(ActiveCell) = 1 And ActiveCell.Value = 1 Then

ActiveCell.Value = "Male"

End If

If Len(ActiveCell) = 1 And ActiveCell.Value = 2 Then

ActiveCell.Value = "Female"

End If

Next

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Double click on This Workbook in the Microsoft Excel Objects, upper left.

Change the 'General' dropdown menu to 'Workbook'.

Select 'SheetChange' from the dropdown menu to the right.

Type "Convert_to_MF"  (no quotes) into the space between Private Sub Workbook_Sheet Change and End Sub.

Close back to Excel.

Now when you enter a 1 or 2 into any sheet in the workbook, Male or Female will replace the 1 or 2.

Answer 2

Easiest way to do this  is use another column and an IF statement.

example - col a contains 1 or 2 -     in another column such as C

in C3 type     =IF(A3=1, "male", "female"). Then copy this formula for as many rows as needed.  If you then wanted to replace column A with male/female,  You could copy col C then in col A  use Paste Special - values