Extract a percentage from a text

[Lok Tak Cheong]

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]

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]

Thanks a lot!

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]

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

Thanks a lot!

 

[Guest]

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

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

 

[Dave Peterson]

And look at VBA's help for instr. It'll do the equivalent of the worksheet
function Find.

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