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 leftaligned, as shown in Figure 1.
Figure 1: Textbased numbers are typically leftaligned.
In this article I'll discuss two approaches you can use to convert such textbased 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 CtrlH 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 CtrlH 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 textbased 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 textbased 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 CtrlF1 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 Atlantabased 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.
You may like these other stories...
The following list highlights 10 apps that that may be of interest to you, your clients, or your clients' clients. They were featured during a session of AWEBLive!, the 12hour CPE marathon, and presented by Gregory L....
I am a recent MS Accounting degree graduate and I am looking into a programming/IT related career.
Anyone here have experience or know any accountants that diverted their careers into IT/Programming/System design, etc?...
Event Date: May 29, 2014, 2 pm ET In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to...
Email signup
Upcoming CPE Webinars
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for nonprofit organizations' revenues. It will include discussions of revenue recognition for cash and noncash contributions as well as other revenues commonly received by nonprofit organizations.
Apr 30
During the second session of a fourpart series on Individual Leadership, the focus will be on time management a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.
May 1
This material focuses on the principles of accounting for nonprofit organizations’ expenses. It will include discussions of functional expense categories, accounting for functional expenses and allocations of joint costs.