need a formula to find "*" in s string and multiply by preceding and following values
in [General]
|
Prev: How did I populate other cells based on infor from one cell?
Next: When I search this group w/my user name nothing from 2010 comes up
From: Roger Govier on 21 Apr 2010 08:31 Hi Tonso You could also create a named Formula Insert>name>define Name Result Refers to =EVALUATE(RIGHT(A1,9)) Now in any cell to the right of your string of data, enter =Result and copy down -- Regards Roger Govier Tonso wrote: > On Apr 19, 6:16 pm, "T. Valko" <biffinp...(a)comcast.net> wrote: >> Improvement... >> >> We can eliminate the SUBSTITUTE function. >> >> =IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),MID(A1,FIND("*",A1)-4,4)*MID(A1,FIND(�"*",A1)+1,5)) >> >> -- >> Biff >> Microsoft Excel MVP >> >> "T. Valko" <biffinp...(a)comcast.net> wrote in message >> >> news:ewesk0A4KHA.4332(a)TK2MSFTNGP02.phx.gbl... >> >> >> >>> Try this... >>> =IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),SUBSTITUTE(MID(A1,FIND("*",A1)-4,5),"�*","")*MID(A1,FIND("*",A1)+1,5)) >>> -- >>> Biff >>> Microsoft Excel MVP >>> "Tonso" <wthoma...(a)hotmail.com> wrote in message >>> news:448459c6-6125-49bd-9135-15193fda58fe(a)n33g2000pri.googlegroups.com... >>>> I have cells enrties which can look like this: >>>> Cell Entries: >> >>>> Desired Results >>>> Cars >>>> 6 6 >>>> Trucks >>>> 11 11 >>>> Bikes >>>> 4*3 12 >>>> Mowers 31*100 >>>> 3100 >>>> They might have an * in them, or not. If there is not an asteric, i >>>> want to, in an adjacent cell, to have the value 6, as shown under >>>> "Desired results" for Cars, or, 3100 (31*100), as shown under Desired >>>> results for Mowers. If there is no asteric, the number can be from 1 >>>> to 3 digits long. If there is an asteric, the number to the left of it >>>> can be from 1 to 3 digits long, and the number to the right can be >>>> from 1-5 digits long. There will always be 10 spaces from the word to >>>> the number, so that for example, the Len of "Cars 6" is 15 (4 >>>> + 10 + 1), and the length of "Bikes 4*3" is 18 (5 + 10 + 3). I >>>> have been unscucessful in creating a formula that will give me the >>>> value, or, if "*", is present, perform the multiplication. >>>> Thanks, >>>> Tonso- Hide quoted text - >> - Show quoted text - > > Biff, > > Thank you very much for your expert help. That did the trick!! > > > Tonso |