» Converting Numbers with Trailing Minus Signs to Normal Negative Numbers
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Column A contains an imported list of positive and negative numbers.
For negative numbers, the minus sign appears to the right, rather than to the left.
We want to convert all the numbers with trailing minus signs to normally-signed negative numbers.
Solution:
1. Use the RIGHT and SUBSTITUTE functions as shown in the following formula in column B:
=IF(RIGHT(A2,1)="-",SUBSTITUTE(A2,"-","")*-1,A2)
2. Use the VALUE, IF, RIGHT, LEFT, and LEN functions as shown in the following formula in column C:
=VALUE(IF(RIGHT(A2,1)="-",RIGHT(A2,1)&LEFT(A2,LEN(A2)-1),A2))
Book Store:
Recommended Books:
- The One Page Business Plan: Start With a Vision, Build a Company!
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- The Guide to Understanding Financial Statements
- Excel Charts
- Accounting and Financial Fundamentals for Nonfinancial Executives
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
No comments have been submitted.
