Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Numeric Values
Copy the data to a worksheet. Then use the menu Data |
Text to Columns | Delimited and check Space. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN | Excel Discussion (Misc queries) | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
Test IF Two Numeric Values Match (from two separate Dynamic Ranges) | Excel Worksheet Functions | |||
Separate numeric and alpha | New Users to Excel |