Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Remove Text from a number

Posted on 2011-03-17
4
Medium Priority
?
188 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

715 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