CODEWITHSUNDEEP
excelexcel-formula
Alex Zemtzov
Alex Zemtzov

Reputation: 17

Excel two types of data in the same cell

I would like to have a list of values in one cell, while when the user looks at it shows text, and when I read this cell I will have a number.

Example:

In cell: A1:, list of options are: TCP, UDP, where TCP=0, UDP=1

I will do in another cell "=A1+4", I will get either 0 or 1 from A1 plus 4

Upvotes: 0

Views: 631

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19792

Use a custom number format of [=1]U\DP;[=0]TCP

  • Right click the cell A1.
  • In the Number tab of the Format Cells dialog box select the Custom category.
  • Add [=1]U\DP;[=0]TCP as the Type and press OK.

Now when you enter data into cell A1 you'll get:

  • When 1 is entered you'll see UDP,
  • When 0 is entered you'll see TCP.
  • Any other number will return #########.
  • Text will display as normal.

Enter =A1+4 in another cell and it automatically picks up the format from A1 so change the format back to General.

  • 0 returns 4
  • 1 returns 5
  • 2 displays 6 in your formula cell, and ######## in A1 so may need to allow for that.
    But, if you've got Data Validation to allow a List of 0,1 then problem solved.

Custom number formats tutorial on ExcelJet

Upvotes: 1

Related Questions