How do I create a number format for a cell with both text and a number?
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.
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)
ExtractNumber = result
Public Function sumExtractNumber(sinput As Range) As Double
For Each Rng In sinput
If IsNumeric(ExtractNumber(Rng.Value)) Then
result = result + ExtractNumber(Rng.Value)
sumExtractNumber = result
For example, if A1 contains *123 and A2 contains #100
=ExtractNumber(A1) will return 123
=sumExtractNumber(A1:A2) will return 223
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