find % number from text string
 

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?

find % number from text string
 

The following UDF will display the percent value as a string:

Function percentality(s As String) As String
parts = Split(s, "%")
MsgBox (parts(0))
t = Right(parts(0), 2)
MsgBox (t)
If IsNumeric(t) Then
percentality = t & "%"
Exit Function
End If
t = Right(parts(0), 1)
If IsNumeric(t) Then
percentality = t & "%"
Exit Function
End If
percentality = ""
End Function

so if A1 contains:
there is 12% by volume
then
=percentality(A1) will display:
12%
as text

--
Gary''s Student - gsnu2007


"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?

find % number from text string
 

It's fairly difficult to come up with a good solution when there is only a
single example to go by.

If the % is always the first number in the string:

=IF(ISNUMBER(FIND("%",C1)),LOOKUP(1E10,--MID(C1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C1&"01234567 89")),ROW(INDIRECT("1:255")))),0)

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"natek1234" wrote in message
ups.com...
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?

find % number from text string
 

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

find % number from text string
 

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