isolate number from string of text

I have output data from a program, .txt files, with multiple lines of text
with one or two numbers on each line. The number I want to isolate is
always in-between a descriptor, like x or y, then a units descriptor at the
end, like lbm^2. Here are some example lines from the output file.

I 676158.2296 lbm in^2

Axis

X 0.9881 in

Y -0.0059 in

Z -0.1538 in



The numbers do have a variable number of decimal places, like 0.9881 or
0.99, so the number of characters in each line can vary.

Is there a way to have excel isolate the numbers that have the decimal in
them?


Thanks,

Stephen R.

On Thu, 4 Aug 2005 08:27:46 -0400, "Stephen R" <nospam@nospam.com> wrote:

>I have output data from a program, .txt files, with multiple lines of text
>with one or two numbers on each line. The number I want to isolate is
>always in-between a descriptor, like x or y, then a units descriptor at the
>end, like lbm^2. Here are some example lines from the output file.
>
> I 676158.2296 lbm in^2
>
> Axis
>
> X 0.9881 in
>
> Y -0.0059 in
>
> Z -0.1538 in
>
>
>
>The numbers do have a variable number of decimal places, like 0.9881 or
>0.99, so the number of characters in each line can vary.
>
> Is there a way to have excel isolate the numbers that have the decimal in
>them?
>
>
>Thanks,
>
>Stephen R.
>

It's probably simplest to create a UDF (user defined function).

It can be done with worksheet formulas, but as a single cell formula, there
will be an error if there is no number in the contents; and in my solution,
there is too much nesting to eliminate that error with a formula.

However, for a 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 the UDF, in some cell enter =GetNum(A1) where A1 contains your data.

====================================
Option Explicit

Function GetNum(str As String)
Dim i As Long
Dim temp As String

GetNum = ""

For i = 1 To Len(str)
temp = Mid(str, i, Len(str) - i + 1)
If Val(temp) <> 0 And IsNumeric(Val(temp)) Then
GetNum = Val(temp)
Exit Function
End If
Next i
End Function
================================

If you really want to use worksheet functions, you can try this
**array-entered** formula:

=--MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(
INDIRECT(FIND(" ",A1)&":"&25)),1)),0)-1,FIND(" ",A1,
MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT(
FIND(" ",A1)&":"&25)),1)),0)+1)-MATCH(TRUE,
ISNUMBER(-MID(A1,ROW(INDIRECT(FIND(
" ",A1)&":"&25)),1)),0)+1)

To **array-enter** a formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.

The above formula will give #N/A if the data does not contain a valid number.
You can eliminate this display by conditional formatting.

Another option would be to use a helper column (or two) to compute the
different parameters of the formula; and then use an IF(... statement to test
for valid output.


--ron

Try the below. It will iterate through each character building a
concatenated string that have values that are numeric, a minus sign, or a
decimal.

To test this, put each value in a cell and select a cell then run the code.

Let me know if that helps any.

Chris

Sub test()
Dim iPos As Integer
Dim sText As String
Dim sNewText As String
Dim i As Integer
Dim iLen As Integer

sText = ActiveCell.Value
iLen = Len(sText)
For i = 1 To iLen
If IsNumeric(Mid(sText, i, 1)) Or Mid(sText, i, 1) = "-" Or
Mid(sText, i, 1) = "." Then
sNewText = sNewText & Mid(sText, i, 1)
End If
Next
ActiveCell.Offset(0, 1).Value = CStr(sNewText)
End Sub


"Stephen R" <nospam@nospam.com> wrote in message
news:%23O4l%23APmFHA.3256@TK2MSFTNGP12.phx.gbl...
>I have output data from a program, .txt files, with multiple lines of text
>with one or two numbers on each line. The number I want to isolate is
>always in-between a descriptor, like x or y, then a units descriptor at the
>end, like lbm^2. Here are some example lines from the output file.
>
> I 676158.2296 lbm in^2
>
> Axis
>
> X 0.9881 in
>
> Y -0.0059 in
>
> Z -0.1538 in
>
>
>
> The numbers do have a variable number of decimal places, like 0.9881 or
> 0.99, so the number of characters in each line can vary.
>
> Is there a way to have excel isolate the numbers that have the decimal in
> them?
>
>
> Thanks,
>
> Stephen R.
>

Stephen R wrote...
>I have output data from a program, .txt files, with multiple lines of text
>with one or two numbers on each line. The number I want to isolate is
>always in-between a descriptor, like x or y, then a units descriptor at the
>end, like lbm^2. Here are some example lines from the output file.
>
> I 676158.2296 lbm in^2
>
> Axis
>
> X 0.9881 in
>
> Y -0.0059 in
>
> Z -0.1538 in
>
>The numbers do have a variable number of decimal places, like 0.9881 or
>0.99, so the number of characters in each line can vary.

If the number is always the second space-separated field, it'd be
easiest to copy cells like this and use Data > Text to Columns on the
copy, using the Delimited option with space as a field delimiter, then
use the 3rd step of the wizard to skip all but the second field.

You could also isolate the second space-separated field with formulas.

=LEFT(TRIM(MID(x,FIND(" ",x&" ")+1,256)),
FIND(" ",TRIM(MID(x,FIND(" ",x&" ")+1,256))&" ")-1)

On 4 Aug 2005 09:21:11 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:

>Stephen R wrote...
>>I have output data from a program, .txt files, with multiple lines of text
>>with one or two numbers on each line. The number I want to isolate is
>>always in-between a descriptor, like x or y, then a units descriptor at the
>>end, like lbm^2. Here are some example lines from the output file.
>>
>> I 676158.2296 lbm in^2
>>
>> Axis
>>
>> X 0.9881 in
>>
>> Y -0.0059 in
>>
>> Z -0.1538 in
>>
>>The numbers do have a variable number of decimal places, like 0.9881 or
>>0.99, so the number of characters in each line can vary.
>
>If the number is always the second space-separated field, it'd be
>easiest to copy cells like this and use Data > Text to Columns on the
>copy, using the Delimited option with space as a field delimiter, then
>use the 3rd step of the wizard to skip all but the second field.
>
>You could also isolate the second space-separated field with formulas.
>
>=LEFT(TRIM(MID(x,FIND(" ",x&" ")+1,256)),
>FIND(" ",TRIM(MID(x,FIND(" ",x&" ")+1,256))&" ")-1)

I like both of your approaches. But for the second, I would suggest a slight
modification:

=LEFT(MID(TRIM(x),FIND(" ",TRIM(x)&" ")+1,256),
FIND(" ",MID(TRIM(x),FIND(" ",TRIM(x)&" ")+1,256)&" ")-1)

only because when I copied the OP's data, there were leading spaces on some of
the strings, and the above enables your formula to work with or without leading
spaces.


--ron

Thank you all for the responses. I had limited success with the programming
suggestions since I am not a programmer by trade.

However, Harlan's suggestion of Data>Text to Columns made me discover a
workflow that works well.

I copy the pertinent section from a data file then right click and paste
into cell A1. Excel automatically converts the data to column format (I was
unaware of this before). It is an extra step outside of Excel, but easily
managed. This way, I can share this pre-formatted spreadsheet, which will
then re-arrange the pasted data for future inclusion into our main chart.

I also learned that Excel has a lot more under the hood than I previously
knew.


Thanks again,

Stephen R.



"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1123172471.021319.17000@g44g2000cwa.googlegroups.com...
> Stephen R wrote...
>>I have output data from a program, .txt files, with multiple lines of text
>>with one or two numbers on each line. The number I want to isolate is
>>always in-between a descriptor, like x or y, then a units descriptor at
>>the
>>end, like lbm^2. Here are some example lines from the output file.
>>
>> I 676158.2296 lbm in^2
>>
>> Axis
>>
>> X 0.9881 in
>>
>> Y -0.0059 in
>>
>> Z -0.1538 in
>>
>>The numbers do have a variable number of decimal places, like 0.9881 or
>>0.99, so the number of characters in each line can vary.
>
> If the number is always the second space-separated field, it'd be
> easiest to copy cells like this and use Data > Text to Columns on the
> copy, using the Delimited option with space as a field delimiter, then
> use the 3rd step of the wizard to skip all but the second field.
>
> You could also isolate the second space-separated field with formulas.
>
> =LEFT(TRIM(MID(x,FIND(" ",x&" ")+1,256)),
> FIND(" ",TRIM(MID(x,FIND(" ",x&" ")+1,256))&" ")-1)
>

Ron,

Your code suggestion is the one that works with my data files.

When I started a new worksheet to test, I realized my previous statement of
Excel automatically converting the pasted data to columns is not true. But
when I tried your code, it worked with all the areas that I need it to.

Thanks again,

Stephen R.




"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:mfi4f11tkufnd46d2b1e7dgjicp4ivd4tm@4ax.com...
> On 4 Aug 2005 09:21:11 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:
>
>>Stephen R wrote...
>>>I have output data from a program, .txt files, with multiple lines of
>>>text
>>>with one or two numbers on each line. The number I want to isolate is
>>>always in-between a descriptor, like x or y, then a units descriptor at
>>>the
>>>end, like lbm^2. Here are some example lines from the output file.
>>>
>>> I 676158.2296 lbm in^2
>>>
>>> Axis
>>>
>>> X 0.9881 in
>>>
>>> Y -0.0059 in
>>>
>>> Z -0.1538 in
>>>
>>>The numbers do have a variable number of decimal places, like 0.9881 or
>>>0.99, so the number of characters in each line can vary.
>>
>>If the number is always the second space-separated field, it'd be
>>easiest to copy cells like this and use Data > Text to Columns on the
>>copy, using the Delimited option with space as a field delimiter, then
>>use the 3rd step of the wizard to skip all but the second field.
>>
>>You could also isolate the second space-separated field with formulas.
>>
>>=LEFT(TRIM(MID(x,FIND(" ",x&" ")+1,256)),
>>FIND(" ",TRIM(MID(x,FIND(" ",x&" ")+1,256))&" ")-1)
>
> I like both of your approaches. But for the second, I would suggest a
> slight
> modification:
>
> =LEFT(MID(TRIM(x),FIND(" ",TRIM(x)&" ")+1,256),
> FIND(" ",MID(TRIM(x),FIND(" ",TRIM(x)&" ")+1,256)&" ")-1)
>
> only because when I copied the OP's data, there were leading spaces on
> some of
> the strings, and the above enables your formula to work with or without
> leading
> spaces.
>
>
> --ron

On Thu, 4 Aug 2005 13:27:44 -0400, "Stephen R" <nospam@nospam.com> wrote:

>Ron,
>
>Your code suggestion is the one that works with my data files.
>
>When I started a new worksheet to test, I realized my previous statement of
>Excel automatically converting the pasted data to columns is not true. But
>when I tried your code, it worked with all the areas that I need it to.
>
>Thanks again,
>
>Stephen R.

Stephen,

I'm glad you have it working for you. Thank you for the feedback.


--ron