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 email@example.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.
Voice of the Editor
Which isn’t completely true. I mean, occasionally I drop by when I manage to sneak out of the nonstop frat party over at Going Concern, but I’m mostly a wallflower over there. I’m happy to say that I’ve been given express permission (or explicit orders, if you like) to wander over here to AccountingWEB more often.
Why is that, you might ask? My job is to replace the irreplaceable Gail Perry as Editor-in-Chief. What does that mean? I don’t really know! I think it’ll be fun getting a feel for things, throwing in my own thoughts here and there, and listening to the discussions you’re having about the accounting profession.