Solved

Remove Text from a number

Posted on 2011-03-17
4
185 Views
Last Modified: 2012-08-14
I have a column of numbers formatted like this $-175.15 USD. How to total the column and remove the USD ?

0
Comment
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
  • Learn & ask questions
  • 3
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
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

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

by:isnoend2001
ID: 35160283
Thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question