Mar 23rd 2010

0

By David H. Ringstrom, CPA

Advert Advertise with us

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:

A1: <1>

A2: 20

A3: <30>

A4: 400

A5: <5,000>

2. Enter this formula in Cell B2, as shown in Figure 4:

=IF(ISNUMBER(A1),A1,-VALUE(MID(A1,2,LEN(A1)-2)))

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:

#,##0.00_);<#,##0.00>

Alternatively, use this code if you want to format the numbers with dollar signs:

$#,##0.00_);<$#,##0.00>

Or, insert [Red] if you with to make negative numbers red:

#,##0.00_);[Red]<#,##0.00>

- 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**david@acctadv.com**or follow him on**Twitter**. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner**CPE Link**.*

## Replies

## Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.