# Excel Tip: Converting Numbers Formatted as <1> to -1

by AccountingWEB on
printer friendly

*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.

**Text-based numbers are typically left-aligned.**

*Figure 1:*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

**Replace < characters with a minus sign.**

*Figure 2:*

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

**Replace > characters with a blank field.**

*Figure 3:*

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)))

**You can use a formula to convert text-based numbers to values.**

*Figure 4:*

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>

**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*[2]*or follow him on**Twitter*[3]*. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner**CPE Link*[4]*.*