isolate number from string of text

  • Thread starter Thread starter Stephen R
  • Start date Start date
S

Stephen R

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.
 
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.
Click to expand...

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
 
Here is my assumption... your data has a space before and after th
number you desire to isolate...


=MID(A1,FIND(" ",A1),FIND(".",A1)-FIND(
",A1))+MID(A1,FIND(".",A1),LEN(A1)-FIND(" ",A1,FIND(".",A1)))

Make sure you format your destination cell as either general or numbe
(and assign the correct amount of decimal places).

Does this work for you?

Bruc
 
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 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.
Click to expand...

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)
 
Stephen R wrote...

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)
Click to expand...

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.
 
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,

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.
Click to expand...

Stephen,

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


--ron
 
Back
Top