Help with strings & formulas

**daisydaisy** · 12-20-2010, 11:48 PM

Hi,

So if a cell reads 10/2APB$9M
I want the next cell to automatically read $9,000,000.00.

Is there a formula to make this happen???

**Paul** · 12-21-2010, 12:18 AM

Hi Daisy, welcome to the forum.

Yes, it is possible to do with a formula, however it would be best to know all possible multipliers you might use, and would there ever be anything after the $, the amount and the multiplier?

Perhaps upload a spreadsheet with a handful of sample data, and the results you would expect to see manually entered next to it.

**rockets2010** · 12-21-2010, 12:49 AM

This may not be the most elegant solution, but it should work assuming that all of your cells are formatted roughly the same (i.e. a "$" sign followed by a value in millions). If this is not the case, this solution would need to be modified or changed completely.

Try this (putting the input data in cell A1):

=LEFT(MID(A1,FIND("$",A1)+1,LEN(A1)), LEN(MID(A1,FIND("$",A1,5)+1,LEN(A1)))-1)*1000000

**Paul** · 12-21-2010, 12:54 AM

Good work, Rockets. Elegance isn't all it's cracked up to be anyway. To account for more than just 1 million as the multiplier, you could change *1000000 to another function that checks for K, M or B (thousand, million, billion), e.g.

=LEFT(MID(A1,FIND("$",A1)+1,LEN(A1)), LEN(MID(A1,FIND("$",A1)+1,LEN(A1)))-1)*LOOKUP(RIGHT(A1,1),{"B","K","M"},{1000000000,1000,1000000})

Then just format that cell as currency/accounting.

**daisydaisy** · 12-21-2010, 03:44 AM

you guys are amazing!!!

Quite stunning the things excel can do! I was asked this question in an interview, and I didn't know the answer. My response was "I haven't had experience using that kind of formula, but I'm sure there's a reference book or an excel online forum that could help me figure it out."

I'll send the interviewer the answer tomorrow morning. Thanks guys!!! Can I ask where you learned these tricks and formulas? Is there a book you'd recommend to master excel?

**teylyn** · 12-21-2010, 03:55 AM

Good answer to the interview question! You don't have to know everything, as long as you have resources to source the knowledge.

Can I ask where you learned these tricks and formulas?

The best way to learn is to teach.

Stick around a forum like this one. Try to answer questions and take in the other suggestions offered. Even if at first you don't think you have anything to contribute, and feel lucky to get in one post a day, if you follow the questions here and work on understanding the answers suggested, your Excel knowledge will multiply within a few months.

**squiggler47** · 12-21-2010, 05:06 AM

=SUBSTITUTE(RIGHT(C4,LEN(C4)-FIND("$",C4)),"M",",000,000.00")
is also an option!

=substitute(substitute(SUBSTITUTE(RIGHT(C4,LEN(C4)-FIND("$",C4)),"M",",000,000.00"),"B",",000,000,000.00"),"K",",000.00")

Allows for K and B as well

**squiggler47** · 12-21-2010, 05:48 AM

Or!

=SUBSTITUTE(RIGHT(C4,LEN(C4)-FIND("$",C4)),RIGHT(C4,1),REPT(",000",FIND(RIGHT(C4,1),"KMBT")))

**daisydaisy** · 12-24-2010, 05:14 AM

Hi guys,

So I just wanted to follow up with you guys. I e-mailed the interviewer with the formulas everyone contributed, and he was thoroughly impressed. He also recommended this formula:

=LEFT(RIGHT(A1,2))*1000000

I gave you guys all the credit

Thanks again!

**squiggler47** · 12-25-2010, 01:48 PM

I think the original question is a bit unfair, as there are no constraints on what values can appear!

Everyone Ignored the simple formula as the assumption that 10M could occur, his formula fails!

Taking the extra steps allow for cases we assume would occur 9.4M etc, KMBT is a little overkill, but give options!