Solved

# Remove Text from a number

Posted on 2011-03-17
185 Views
I have a column of numbers formatted like this \$-175.15 USD. How to total the column and remove the USD ?

0
Question by:isnoend2001
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 81

Expert Comment

ID: 35160127
You can use the Replace command. Select the cells to edit, press CTRL+H, enter " USD" in the Find field and leave the replace field empty. Replace all.

Kevin
0

LVL 81

Expert Comment

ID: 35160196
You can also use this formula to add the values WITHOUT first removing the USD and \$:

=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A1:A2," USD",""),"\$","")))

Enter the formula by pressing CTRL+SHIFT+ENTER.

Kevin
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 35160208
A different formula that does not require special entry:

=SUMPRODUCT(VALUE(SUBSTITUTE(SUBSTITUTE(A1:A2," USD",""),"\$","")))

Kevin
0

Author Closing Comment

ID: 35160283
Thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month4 days, 5 hours left to enroll