I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

For your one example you could use: =MID(A1,9,3)

(assuming the data is in cell A1)

"JW" wrote:

I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

Sorry, I wasn't clear enough. It is all in one cell. The 9 has to be
dynamic somehow because the position changes. Here are some more
examples of my data. The only thing I'm sure of is that my number is
between the + and the %.

XR60 (P+4.65%)
BB60 (P+5.15%)
RT61 (P+5.6%)
UU02 (P+6%)
Y661 (P+6.15%)
C26C (P+6.6%)

Appreciate the help!

tim m wrote:
For your one example you could use: =MID(A1,9,3)

(assuming the data is in cell A1)

"JW" wrote:

I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

Are you up for a quick macro?

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

On 20 Jul 2006 13:52:01 -0700, "JW" wrote:

I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

JW

Ignore my post.

I replied based on your example of ABSD (P+3.9%)

Won't work with your second posting examples.


Gord Dibben MS Excel MVP

On 20 Jul 2006 14:21:00 -0700, "JW" wrote:

Sorry, I wasn't clear enough. It is all in one cell. The 9 has to be
dynamic somehow because the position changes. Here are some more
examples of my data. The only thing I'm sure of is that my number is
between the + and the %.

XR60 (P+4.65%)
BB60 (P+5.15%)
RT61 (P+5.6%)
UU02 (P+6%)
Y661 (P+6.15%)
C26C (P+6.6%)

Appreciate the help!

tim m wrote:
For your one example you could use: =MID(A1,9,3)

(assuming the data is in cell A1)

"JW" wrote:

I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

THIS WORKS PERFECTLY:
Assume that your data is in cell A1, enter fomula in cell b1 and enjoy:
=MID(A1;FIND("+";A1)+1;FIND("%";A1)-FIND("+";A1))

greetings
"JW" wrote in message
oups.com...
I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

dont you thing my formula is a little bit easier for non developers?
but, you are quick... this vba in little time... :-)
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Are you up for a quick macro?

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

On 20 Jul 2006 13:52:01 -0700, "JW" wrote:

I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

I'm using ";" as list separator - you should use ";" or "," depending on
your regional settings
"Sasa Stankovic" wrote in message
...
THIS WORKS PERFECTLY:
Assume that your data is in cell A1, enter fomula in cell b1 and enjoy:
=MID(A1;FIND("+";A1)+1;FIND("%";A1)-FIND("+";A1))

greetings
"JW" wrote in message
oups.com...
I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW

Yep--that's the winner!! Thanks Sasa and everyone else who responded!

Sasa Stankovic wrote:
THIS WORKS PERFECTLY:
Assume that your data is in cell A1, enter fomula in cell b1 and enjoy:
=MID(A1;FIND("+";A1)+1;FIND("%";A1)-FIND("+";A1))

greetings
"JW" wrote in message
oups.com...
I have some data that I want to extract a number from. Here is an
example.

ABSD (P+3.9%)

I want to get at the 3.9, and I'm assuming I would want to use the "+"
sign and the "%" sign as delimiters somehow. Is there a function that
would help me do this all in one step?

I can get there using text to columns, but that is very manual and
requires several steps.

I'm out of ideas!!

Thanks.

--

JW