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

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

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

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

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

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

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