| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to find "*" in s string and multiply by preceding andfollowing values
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need a formula to find "*" in s string and multiply by preceding and following values
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need a formula to find "*" in s string and multiply by preceding and following values
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" wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need a formula to find "*" in s string and multiply by precedingand following values
On Apr 19, 6:16*pm, "T. Valko" 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" wrote in message ... 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" wrote in message .... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need a formula to find "*" in s string and multiply by preceding and following values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tonso" wrote in message ... On Apr 19, 6:16 pm, "T. Valko" 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting a text strings but omitting preceding "A" or "The" | Excel Worksheet Functions | |||
| Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
| text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
| Isolate text immediately preceding "(" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Linear Mode
