# 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.

### Abuse history

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

Human Resources

### Abuse history

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

="A"&TEXT(A1,"0,000")

It will give you A1,000,000

Here is some more info on Text formula -
**TEXT FORMULA**

Cheers

Sumit Bansal

TrumpExcel.com