By default If there is text with numbers in excel cell, then that numbers are also counted as text format. So that no formulas can be used in this type of data. But to achieve it this can be possible with the use of custom number format.
Example :
See the image below. we have data in column B and column C that contains numbers and text both in a cell. Now we want to multiply column B with column C. But the formula doesn’t work because the data in column B and column C also have text with numbers. An error occurs.
This problem can be solved by using custom number format. The data in column B and column C will need to be converted from text format to number format. Follow the Steps Below.
Note:- Follow the steps below before data entry.
- Select cells B4:B7 or column B
- Right click on it.
- Click Format cell > Numbers Tab > Custom
- In the Type box, Type:- # ” kg”
- Click OK.
- Select cells C4:C7 or column C
- Right click on it.
- Click Format cell > Numbers Tab> Custom
- In the Type box, Type:- “Rs.” #
- Click OK.
Enter your data in column B and column C. You can see that numbers have been converted from text format to number format and now the formulas will work on this data. See the image bellow:
