Split and Format Data for Quick Calculation | How to Calculate Total in Excel with Custom Formatting

Steps for Formatting Data in Excel
Use Text-to-Columns to Split Data
Format Columns to Remove Extra Text
Calculate Totals Using Formatting
Example Data
Assume your data starts in cell A2:
Step-by-Step Guide
1. Use Text-to-Columns to Split Data
Select the range where you have your data, starting from cell A2 downwards, for both QTY and PRICE columns (e.g., B2:C8).
Go to the Data tab and click on Text to Columns.
Choose Delimited and click Next.
Select Space and Other. In the Other box, enter a vertical bar (|).
Click Finish. This will split the data at spaces and the pipe character, separating the numeric values from the text.
2. Format Columns to Remove Extra Text
Select column B (QTY):
Right-click and choose Format Cells.
Go to the Number tab.
Select Custom.
Enter 0" PCS" in the Type box.
Click OK.
Select column C (PRICE):
Right-click and choose Format Cells.
Go to the Number tab.
Select Custom.
Enter 0" RS" in the Type box.
Click OK.
Select column D (TOTAL):
Right-click and choose Format Cells.
Go to the Number tab.
Select Custom.
Enter 0" RS" in the Type box.
Click OK.
3. Calculate Totals Using Formatting
Select the cell below the last entry in QTY (e.g., B9):
Enter the formula:
excel
Copy code
=SUM(B2:B8)
Format the cell as 0" PCS".
Select the cell below the last entry in PRICE (e.g., C9):
Enter the formula:
excel
Copy code
=SUM(C2:C8)
Format the cell as 0" RS".
Select the cell below the last entry in TOTAL (e.g., D9):
Enter the formula:
excel
Copy code
=SUM(D2:D8)
Format the cell as 0" RS".
Example Formatted Sheet
Here’s how your Excel sheet will look after formatting:
Notes
Text-to-Columns separates the numeric value and the text based on delimiters.
Custom Number Format keeps the text as a suffix in the display without altering the numeric value.