Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
A1 is text: The increment is 5%
B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
The number is always the last thing in the string and it always has a space
character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
Thanks a lot!
"Dave Peterson" wrote in message ... The number is always the last thing in the string and it always has a space character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
Dear Dave:
Could you please tell me what is the problem of this function? Function efind(Find_value, Within_Text) E = WorksheetFunction.Find(Find_value, Within_Text) efind = WorksheetFunction.Mid(Within_Text, E - 1, 2) End Function "Lok Tak Cheong" wrote in message ... Thanks a lot! "Dave Peterson" wrote in message ... The number is always the last thing in the string and it always has a space character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
Assuming the value is actually in the text being searched (also Find is case
sensitive), probably because VBA has it's own Mid function - so drop the worksheetfunction.mid and change to just mid. Function efind(Find_value, Within_Text) E = WorksheetFunction.Find(Find_value, Within_Text) efind = Mid(Within_Text, E - 1, 2) End Function "Lok Tak Cheong" wrote: Dear Dave: Could you please tell me what is the problem of this function? Function efind(Find_value, Within_Text) E = WorksheetFunction.Find(Find_value, Within_Text) efind = WorksheetFunction.Mid(Within_Text, E - 1, 2) End Function "Lok Tak Cheong" wrote in message ... Thanks a lot! "Dave Peterson" wrote in message ... The number is always the last thing in the string and it always has a space character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
And look at VBA's help for instr. It'll do the equivalent of the worksheet
function Find. Lok Tak Cheong wrote: Dear Dave: Could you please tell me what is the problem of this function? Function efind(Find_value, Within_Text) E = WorksheetFunction.Find(Find_value, Within_Text) efind = WorksheetFunction.Mid(Within_Text, E - 1, 2) End Function "Lok Tak Cheong" wrote in message ... Thanks a lot! "Dave Peterson" wrote in message ... The number is always the last thing in the string and it always has a space character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract a percentage from a text
Dear JMB and Dave:
Thank you very much!!! "Dave Peterson" wrote in message ... And look at VBA's help for instr. It'll do the equivalent of the worksheet function Find. Lok Tak Cheong wrote: Dear Dave: Could you please tell me what is the problem of this function? Function efind(Find_value, Within_Text) E = WorksheetFunction.Find(Find_value, Within_Text) efind = WorksheetFunction.Mid(Within_Text, E - 1, 2) End Function "Lok Tak Cheong" wrote in message ... Thanks a lot! "Dave Peterson" wrote in message ... The number is always the last thing in the string and it always has a space character before it? If yes to both, then this returned 50 for me: =1000*RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Lok Tak Cheong wrote: A1 is text: The increment is 5% B1 is value and formula = 1000 * (increment in A1) Can anyone help with this? Thanks in advance, Cheong -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Text to get a Percentage | Excel Worksheet Functions | |||
refer a cell text in b1 and extract remaing text from a1 | Excel Discussion (Misc queries) | |||
How to extract text from number/text cell | Excel Worksheet Functions | |||
Extract data that has increased by a percentage | Excel Discussion (Misc queries) | |||
Convert Percentage to Text | Excel Discussion (Misc queries) |