On Mon, 17 Sep 2007 17:10:59 -0400, Ron Rosenfeld
wrote:

On Mon, 17 Sep 2007 18:19:12 -0000, natek1234 wrote:

I am trying to come up with a formula to display a percentage in
another cell from a long string of other random text and if none to
show up at 0%. For example, the string in C1 is "(10%, 1, 15hr) blah
blah blah" i need a cell that finds the 10 and displays 10% and if
there is no % to display 0%. can anyone help me?

Here is a UDF that will do that.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer Window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, you can then enter the formula

=PC(cell_ref) into any cell. It will return the %, but you will have to format
the cell to show a % value.

===============================
Option Explicit
Function PC(str As String) As Double
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")

'Pattern to extract any positive or negative
'floating point number followed by a "%"
re.Pattern = "[\-+]?\b\d*\.?\d+(?=%)"

If re.test(str) Then
Set mc = re.Execute(str)
PC = mc(0) / 100
Else
PC = 0
End If
End Function
===============================
--ron

Small change in re.pattern above to take care of the case if you have no
leading zero:

re.Pattern = "[\-+]?\d*\.?\d+(?=%)"
--ron