Excel Tip: Converting Numbers Formatted as <1> to -1
By David H. Ringstrom, CPA
- 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:
- 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.
- 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.
- 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).
- 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:
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 firstname.lastname@example.org or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.