Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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.

Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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.

Need Function for Text To Columns
 

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

Need Function for Text To Columns
 

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