How do I create a number format for a cell with both text and a number?

Zrizt asked on

Currently, I have numbers in a column that range anywhere from 0 to 2,100,000. I have the cells in this column formatted to where when I type in 2000000 it automatically formats it to 2,000,000. Now I have to add a formula to each of these cells that may or may not add a character to the beginning of the number, i.e. A1,500,000 or *1,345,789. However, when I add this formula, the format changes and doesn't display the commas anymore, even when I go back and try to format it again. How do I keep the number format?

Additionally, there is one cell where it sums all of the values in this column. Obviously if there is added text the sum formula will display an error. Is there a workaround where I can sum the values in a column when there is text?

EDIT: The numbers in the column are a part of a formula as well.

1 person had this question

Abuse history

The answered status icon Answer
Wyman W replied on

Bansal has answered your first question. To answer your second question, create a custom function using VBA. I have created two Functions, one is called ExtractNumber, which is to extract the number part of the text. Another Function sumExtractNumber is to extract number part of a text and then sum them up.

ALT+11 > Insert Module > Copy and paste all the below code

Public Function ExtractNumber(sinput) As Double
    For i = 1 To Len(sinput)
        If IsNumeric(Mid(sinput, i, 1)) Then
            result = result & Mid(sinput, i, 1)
        End If
    Next i
    ExtractNumber = result
End Function

Public Function sumExtractNumber(sinput As Range) As Double
    For Each Rng In sinput
        If IsNumeric(ExtractNumber(Rng.Value)) Then
            result = result + ExtractNumber(Rng.Value)
        End If
    Next Rng
    sumExtractNumber = result
End Function

For example, if A1 contains *123 and A2 contains #100

=ExtractNumber(A1) will return 123

=sumExtractNumber(A1:A2) will return 223

Wyman W
Human Resources
Be the first person to mark this helpful

Abuse history

The answered status icon Answer
Bansal Sumit replied on

You can use TEXT function to achieve this. For example, if you have 1000000 in cell A1, and you use the below formula in cell B1


It will give you A1,000,000

Here is some more info on Text formula - TEXT FORMULA

If this answers your question, please mark it as Answer!

Sumit Bansal
Be the first person to mark this helpful

Abuse history