Separate Numeric Values
 

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.

Separate Numeric Values
 

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

Hth,
Merjet

Separate Numeric Values
 

On Mon, 24 Dec 2007 07:38:06 -0800 (PST), wutzke
wrote:

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

Separate Numeric Values
 

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.



On Dec 24, 8:37*am, Ron Rosenfeld wrote:
On Mon, 24 Dec 2007 07:38:06 -0800 (PST), wutzke
wrote:

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

Separate Numeric Values
 

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


"wutzke" wrote in message
...
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.



On Dec 24, 8:37 am, Ron Rosenfeld wrote:
On Mon, 24 Dec 2007 07:38:06 -0800 (PST), wutzke

wrote:

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