Separate Numeric Values

[wutzke]

We have a text file with
04 TRANS10.0 Witem dataMEO 5 2 2 0 37.29 EA 74.57 1.3 55.5
04 TRANS10.5 Mitem dataMEO 10 0 0 0 37.77 EA 0.00 3.8 172.3
04 TRANS10.5 Witem dataMEO 12 3 3 0 37.77 EA 113.31 3.3 154.3
04 TRANS11.0 Mitem dataMEO 6 0 0 0 37.77 EA 0.00 3.8 174.6
04 TRANS11.0 Witem dataMEO 8 2 2 0 37.77 EA 75.54 6.0 254.7
04 TRANS11.5 Mitem dataMEO 2 2 2 0 41.74 EA 83.48 1.9 88.6
04 TRANS11.5 Witem dataMEO 4 1 1 0 37.77 EA 37.77 4.5 207.3
04 TRANS12.0 Mitem dataMEO 5 1 1 0 42.99 EA 42.99 6.4 274.8
04 TRANS12.0 Witem dataMEO 5 0 0 0 43.78 EA 0.00 7.6 345.7

Everything after "dataMEO" are number that need to be separated into
individual cells. The length (of the number) of string is not
consistant.

 

[merjet]

Copy the data to a worksheet. Then use the menu Data |
Text to Columns | Delimited and check Space.

Hth,
Merjet

 

[Ron Rosenfeld]

We have a text file with
04 TRANS10.0 Witem dataMEO 5 2 2 0 37.29 EA 74.57 1.3 55.5
04 TRANS10.5 Mitem dataMEO 10 0 0 0 37.77 EA 0.00 3.8 172.3
04 TRANS10.5 Witem dataMEO 12 3 3 0 37.77 EA 113.31 3.3 154.3
04 TRANS11.0 Mitem dataMEO 6 0 0 0 37.77 EA 0.00 3.8 174.6
04 TRANS11.0 Witem dataMEO 8 2 2 0 37.77 EA 75.54 6.0 254.7
04 TRANS11.5 Mitem dataMEO 2 2 2 0 41.74 EA 83.48 1.9 88.6
04 TRANS11.5 Witem dataMEO 4 1 1 0 37.77 EA 37.77 4.5 207.3
04 TRANS12.0 Mitem dataMEO 5 1 1 0 42.99 EA 42.99 6.4 274.8
04 TRANS12.0 Witem dataMEO 5 0 0 0 43.78 EA 0.00 7.6 345.7

Everything after "dataMEO" are number that need to be separated into
individual cells. The length (of the number) of string is not
consistant.

You could use Data/Text to Columns with <space> as the delimiter and select to
ignore the first four columns (in the wizard).

If you are only interested in the numbers, you can also select to ignore the
column containing EA.
--ron

 

[wutzke]

That's a slick solution, using the Data to Column Wizard.
I was looking for a function I could work cell by cell.
Perhaps a string function with the " EA " as the logical anchor.
FIND(" EA ",A10)

so
=MID(A10,(FIND(" EA ",A10)-6),7)
would get me the value (dollar amount) right before the " EA "

But you see, getting the values that way is tough. If you look at the
1st 3 line, the value after " EA " are 2, 1 & 3 digits long.

 

[Rick Rothstein \(MVP - VB\)]

I have read your original message and this message several times and I am
having trouble figuring out exactly what you want from each of your data
lines. In your original post, you said "everything after 'dataMEO' are
number that need to be separated into individual cells", but you don't say
if you want the numbers after the "EA" also. In your last message, you show
a MID function call to get the number "right before the ' EA '". Later on
you talk about the value after the " EA " as being 2, 1 and 3 digits long
which completely ignores the value after the decimal point. Can you give us
an example? Take the first line and list for us the numbers you need to
extract from it.

Rick


That's a slick solution, using the Data to Column Wizard.
I was looking for a function I could work cell by cell.
Perhaps a string function with the " EA " as the logical anchor.
FIND(" EA ",A10)

so
=MID(A10,(FIND(" EA ",A10)-6),7)
would get me the value (dollar amount) right before the " EA "

But you see, getting the values that way is tough. If you look at the
1st 3 line, the value after " EA " are 2, 1 & 3 digits long.

 

[Ron Rosenfeld]

That's a slick solution, using the Data to Column Wizard.
I was looking for a function I could work cell by cell.
Perhaps a string function with the " EA " as the logical anchor.
FIND(" EA ",A10)

so
=MID(A10,(FIND(" EA ",A10)-6),7)
would get me the value (dollar amount) right before the " EA "

But you see, getting the values that way is tough. If you look at the
1st 3 line, the value after " EA " are 2, 1 & 3 digits long.

It's not really tough, but it is complicated.

Since you want to pull out the numbers after the MEO using a function, the
following assumes that the number of "number groups" are the same in each
entry.

In other words:

04 TRANS10.0 Witem dataMEO 5 2 2 0 37.29 EA 74.57 1.3 55.5

The first value you want to return, the "5" after the MEO, is always going to
be the third number in the string. (04 is the first; 10.0 is the second; and 5
is the third).

One solution is to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

Then, with your data in column A, starting in A1, enter

B1: =REGEX.MID($A1,"\b\d*\.?\d+\b",COLUMNS($A:A)+2)

Select B1 and fill-right to J1

Then select b1:j1 and fill-down as far as required.

If your data does not begin in A1, change the reference in the formula to
reflect the starting cell, but keep the mixed reference fixing the column.
--ron