2

I am trying to create a custom numeric format in Excel that includes text from another cell. The important point is that I want to keep the value in the formatted cell as numeric format. Questions close to this question have been answered before, but they all rely on converting the value into text (usually using TEXT()). I want to be able to do further calculations on this value without having to go through the extra steps of converting it BACK to a number.

The format should be something like 0.00 "B2" , but this just treats the text B2 literally. Is there a way to make it work as a cell reference?

It seems like there should be a basic way to do this, but I can't find it. Does anyone know how?

Improve this question
3
  • 1
    You can't. As soon as you try to concatenate another field in it becomes a string. The best would be to keep it two columns sized correctly to "look" like one column. Commented Aug 29 at 17:45
  • 1
    Would it be acceptable for your use case to have one cell contain the numeric value and another contain the text value with the formatting as you desire? The numeric cell can be hidden from view but other cells that need a value can reference that hidden cell. Commented Aug 29 at 19:03
  • Without vba it's not possible to do as described. You can use Formatting and customize that. There you can add words to the visually presented numerical value, you just cannot use cell references there. Commented Aug 31 at 7:12

1 Answer 1

Reset to default
3

Alternatively, you can use VBA and modify the cell format with the Change event whenever is needed. The following code applies to cell D2 and includes the contents of cell B2 in the format code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nf As String
    If Target.Address = "$D$2" Then
        nf = "0.00 """ & Range("B2").Value & """"
        If Target.NumberFormat <> nf Then Target.NumberFormat = nf
    End If
End Sub

NumberFormat

Improve this answer
1
  • Thanks. I have a barely functional VBA understanding, and this project is not so important to start digging into that. What I've done is keep the number in one cell, and then depending on the value of B2 (the unit) copied that into a different cell formated with the right unit. Commented Aug 29 at 20:06

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.