LinkBack URL
About LinkBacks
Hi,
I have a table in which I copied some data off a webpage.
As you will see in example - There is column A which is the copied inputs from a website.
I wish to convert this to dollar values & then sum total in column B.
How to do this?
Thanks
Text Values to Numerical Dollar Values
Hi,
I have a table in which I copied some data off a webpage.
As you will see in example - There is column A which is the copied inputs from a website.
I wish to convert this to dollar values & then sum total in column B.
How to do this?
Thanks
Last edited by mycon73; 05-14-2016 at 01:23 AM.
MyCon
-- Using Latest Version of Excel
Try this formula in F10 and copy down. Format these cells as Currency.
Formula:
Please Login or Register to view this content.
The *1 at the end forces the text string the RIGHT() section of the formula returns into a numeric value.
BSB
If you want to do this all in 1 cell...
=SUMPRODUCT(--(MID(D10:D23,FIND(" ",D10,1)+1,99)))
Or, use this for each cell...
=--MID(D10,FIND(" ",D10,1)+1,99)
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Or try this ...
=--REPLACE(D10,1,FIND(" ",D10),"")
Here is another way
Enter formula in cell B2 and copy down
Formula:Please Login or Register to view this content.
Last edited by AlKey; 05-13-2016 at 09:15 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi Everyone,
Great Solutions!
However, the, "=LOOKUP(10^308,--RIGHT(A2,ROW(A$1:A$15)))" isn't working for me.
Thanks everyone....
Not sure why you saying it is not working.Originally Posted by mycon73
Please see attached file with the same formula
AK-Text Values to Numerical Dollar Values.xlsx
This worked on your sample data.
Select the range A10:A23
Goto the Data tab>Text to Columns
Select: Delimited
Click: Next
Select: Space
Click: Next
Select: Do not import column (skip)
Click: Finish
The range now contains numeric numbers that can be summed.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi.
Alternatively, you could use Find & Replace - see attached.
(Format the cells to us dollars)
peterrc
@AlKey
The formula failed in the range given in column D because the Row(A$1:A$15) couldn't be changed to D$10:D$23. If left alone or changed to D$1:D$15 the formula works perfectly. I think that the OP didn't make that change.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks