*By David H. Ringstrom, CPA*

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

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

** Figure 2: **Replace < characters with a minus sign.

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

*Figure 3:*

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

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

*Figure 4:*

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

*A previous version of this article first appeared on *www.accountingweb.com* .*

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