By David H. Ringstrom, CPA
From time to time you may encounter financial data where negative numbers are enclosed in brackets, such as <100>. In most cases, Excel treats such numbers as text, which means such numbers won't be included in totals or other arithmetic functions.
Typically such numbers will be left-aligned, as shown in Figure 1.
Figure 1: Text-based numbers are typically left-aligned.
In this article I'll discuss two approaches you can use to convert such text-based inputs to negative numbers. I'll also share a formatting technique you can use if you want to enclose negative numbers in such brackets instead of parentheses.
The first approach for converting a number like <1> to -1 involves Excel's Find and Replace feature:
- Select the range of cells that contain numbers surrounded by < >. It doesn't matter if you select cells that have numbers without brackets, but it's helpful to limit the area that you're searching to avoid unintended replacements.
- In any version of Excel, press Ctrl-H to display the Find and Replace dialog box shown in Figure 1.
- As shown in Figure 2:
a. Enter < in the Find What field
b. Enter - in the Replace With field
c. Click Replace All
Figure 2: Replace < characters with a minus sign.
4. Press Ctrl-H again, and as shown in Figure 3:
a. Enter > in the Find What field
b. Leave the Replace With field blank
c. Click Replace All
Figure 3: Replace > characters with a blank field.
At this point all of the numbers surrounded by < > will be negative numbers and no longer treated as text.
Alternatively, you can tackle this problem formulaically. To do so, I'll use several different Excel functions:
- IF – The IF function allows us to carry out a true/false test, return a result, or perform a calculation based on the result of the test. IF has three arguments: logical_test, value_if_true, and value_if_false.
- ISNUMBER – The ISNUMBER function allows us to test whether a cell contains a number. This function has a single value argument, and returns TRUE if a cell contains a number or FALSE if it contains text or is blank.
- VALUE – The VALUE function converts a number stored as text into a value, and has a single text argument.
- MID – The MID function allows us to extract text from the middle of a string, which is another way to refer to text within a cell. This function has three arguments: text, start_num, and num_chars.
- LEN – The LEN function returns the length of text within a cell, and has a single text argument.
Now let's put these functions together into a single formula.
1. As shown in Figure 1, enter these inputs into a blank worksheet:
2. Enter this formula in Cell B2, as shown in Figure 4:
Figure 4: You can use a formula to convert text-based numbers to values.
As discussed above, IF has three arguments:
- logical_test: the ISNUMBER function determines if the data in Cell A1 is a number or not.
- value_if_true: If ISNUMBER returns true, then Cell A1 contains a number, so I'll simply return that value.
- value_if_false: If ISNUMBER returns false, I know that I have a number surrounded by < > that I wish to remove. In this case I'll use the VALUE and MID functions together.
As shown above, MID has three arguments:
- text – In this case, I refer to Cell A1 for the text I wish to shorten
- start_num – I know that I want to eliminate the starting < character, so I instruct MID to start at the second position
- num_char – I also want to eliminate the trailing >, so I'll use the LEN function to determine how long the text in Cell A1 is, and then subtract 2 from that number. Thus LEN(A1)-2 would return 1 for Cell A1. LEN(A3)-2 returns 2, and LEN(A5,2) returns 5 (the comma counts as a character).
The MID function returns a text-based number, which I convert using the VALUE function. I added a minus sign before VALUE to return a negative number.
Finally, I promised to share a technique that you can use if you want to use < > instead of parentheses to format a spreadsheet:
- In any version of Excel, press Ctrl-F1 to display the Format Cells dialog box.
- Choose Custom from the Number tab.
- Erase the Type field, and enter this format code, as shown in Figure 4:
Alternatively, use this code if you want to format the numbers with dollar signs:
Or, insert [Red] if you with to make negative numbers red:
Read more articles by David Ringstrom.
About the author:
- David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.