Extracting data from a text string:

A

Art MacNeil

Hello all,

I get a text file and I need to extract a specific piece of data from it.

I've tried using Substitute and right and left but they don't work the way
I'd expect.

I'm not proficient with substitute so maybe I'm missing something obvious.

In any case, here's the text file:

Employee D W CL RT Points [Change]
===============================================================
1 Employee # 01 98 31 43 33 6218.50 [-142.57]
2 Employee # 02 81 15 16 8 5231.37 [+142.32] **
3 Employee # 03 98 12 10 20 4376.50 [+144.83] **
4 Employee # 04 98 10 8 12 2863.88 [-43.97] **
5 Employee # 05 84 13 7 12 2523.29 [-61.61]
6 Employee # 06 98 2 3 1 2250.26 [+4.09] **
7 [8] Employee # 07 72 4 2 2 2181.19 [+98.41] **
8 [7] Employee # 08 95 3 1 0 2144.35 [-13.40] **
9 Employee # 09 91 5 5 5 1839.72 [-22.18] **
10 Employee # 10 98 2 1 0 1667.69 [+3.61] **

11 Employee # 11 99 0 2 2 1442.68 [-22.53] **
12 Employee # 12 94 1 0 0 1319.18 [+28.06] **
13 Employee # 13 88 0 0 0 1128.33 [+4.80] **
14 Employee # 14 68 0 0 0 878.10 [+9.50] **
15 Employee # 15 74 0 0 0 765.12 [-16.95]
16 Employee # 16 46 0 0 0 656.71 [-9.47]
17 Employee # 17 36 0 0 0 616.35 [-7.90]
18 Employee # 18 38 0 0 0 548.80 [-2.08] **
19 Employee # 19 24 0 0 0 418.94 [+19.77] **
20 Employee # 20 36 0 0 1 363.01 [-8.34]

21 [22] Employee # 21 20 0 0 0 360.39 [+20.61] **
22 [21] Employee # 22 59 0 0 0 352.42 [-10.97]
23 Employee # 23 20 0 0 1 319.15 [+11.71] **
24 Employee # 24 19 0 0 0 275.27 [-3.86]
25 Employee # 25 27 0 0 0 242.49 [-5.99]
26 Employee # 26 20 0 0 0 193.48 [-3.83]
27 Employee # 27 11 0 0 0 191.05 [-2.30]
28 [29] Employee # 28 7 0 0 0 190.10 [+22.60] **
29 [28] Employee # 29 20 0 0 0 187.65 [-3.62]
30 [42] Employee # 30 2 0 0 1 170.57 [+112.02] **

31 [30] Employee # 31 34 0 0 0 159.26 [-7.13]
32 [31] Employee # 32 18 0 0 0 149.12 [-2.83]
33 [32] Employee # 33 11 0 0 0 117.56 [-1.74]
34 [33] Employee # 34 26 0 0 0 109.85 [-4.97]
35 [34] Employee # 35 14 0 0 0 105.06 [-2.09]
36 Employee # 36 7 0 0 0 89.43 [-0.93]
37 [35] Employee # 37 16 0 0 0 88.62 [-2.45]
38 [37] Employee # 38 14 0 0 0 82.81 [-2.37]
39 Employee # 39 7 0 0 0 79.14 [-0.90]
40 [38] Employee # 40 26 0 0 0 77.55 [-5.59]

41 [44] Employee # 41 3 0 0 0 75.12 [+23.91] **
42 [40] Employee # 42 17 0 0 0 69.32 [-3.70]
43 [41] Employee # 43 4 0 0 0 59.06 [-1.03]
44 [43] Employee # 44 7 0 0 0 54.41 [-1.30]
45 [52] Employee # 45 5 0 0 0 49.45 [+23.90] **
46 [53] Employee # 46 2 0 0 0 47.90 [+29.80] **
47 [47] Employee # 47 4 0 0 0 46.90 [-0.57]
48 [46] Employee # 48 5 0 0 0 46.41 [-1.13]
49 [45] Employee # 49 16 0 0 0 45.60 [-2.45]
50 [48] Employee # 50 3 0 0 0 43.61 [-0.92]

51 [50] Employee # 51 14 0 0 0 37.48 [-2.35]
52 [49] Employee # 52 9 0 2 2 36.54 [-6.49]
53 [51] Employee # 53 20 0 0 0 30.32 [-2.99]
54 Employee # 54 2 0 0 0 20.42 [+2.50] **
55 Employee # 55 10 0 0 0 13.80 [-3.07]
56 Employee # 56 3 0 0 0 5.37 [-0.61]
57 Employee # 57 6 0 0 0 3.78 [-1.06]
58 Employee # 58 6 0 0 0 2.35 [-0.98]



The data I want is the points.

So for:
Employee #01: 6218.50
Employee #02: 5231.37
Etc.


Is there a good formula to extract this information?

Thank you for your help,

Art.

XP, Office 2003.
 
B

Bob Phillips

=MID(TRIM(LEFT(A1,FIND("[",A1)-1)),FIND("~",SUBSTITUTE(TRIM(LEFT(A1,FIND("[",A1)-1)),"
","~",LEN(TRIM(LEFT(A1,FIND("[",A1)-1)))-LEN(SUBSTITUTE(TRIM(LEFT(A1,FIND("[",A1)-1)),"
",""))))+1,255)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Art MacNeil said:
Hello all,

I get a text file and I need to extract a specific piece of data from it.

I've tried using Substitute and right and left but they don't work the way
I'd expect.

I'm not proficient with substitute so maybe I'm missing something obvious.

In any case, here's the text file:

Employee D W CL RT Points [Change]
===============================================================
1 Employee # 01 98 31 43 33 6218.50 [-142.57]
2 Employee # 02 81 15 16 8 5231.37 [+142.32] **
3 Employee # 03 98 12 10 20 4376.50 [+144.83] **
4 Employee # 04 98 10 8 12 2863.88 [-43.97] **
5 Employee # 05 84 13 7 12 2523.29 [-61.61]
6 Employee # 06 98 2 3 1 2250.26 [+4.09] **
7 [8] Employee # 07 72 4 2 2 2181.19 [+98.41] **
8 [7] Employee # 08 95 3 1 0 2144.35 [-13.40] **
9 Employee # 09 91 5 5 5 1839.72 [-22.18] **
10 Employee # 10 98 2 1 0 1667.69 [+3.61] **

11 Employee # 11 99 0 2 2 1442.68 [-22.53] **
12 Employee # 12 94 1 0 0 1319.18 [+28.06] **
13 Employee # 13 88 0 0 0 1128.33 [+4.80] **
14 Employee # 14 68 0 0 0 878.10 [+9.50] **
15 Employee # 15 74 0 0 0 765.12 [-16.95]
16 Employee # 16 46 0 0 0 656.71 [-9.47]
17 Employee # 17 36 0 0 0 616.35 [-7.90]
18 Employee # 18 38 0 0 0 548.80 [-2.08] **
19 Employee # 19 24 0 0 0 418.94 [+19.77] **
20 Employee # 20 36 0 0 1 363.01 [-8.34]

21 [22] Employee # 21 20 0 0 0 360.39 [+20.61] **
22 [21] Employee # 22 59 0 0 0 352.42 [-10.97]
23 Employee # 23 20 0 0 1 319.15 [+11.71] **
24 Employee # 24 19 0 0 0 275.27 [-3.86]
25 Employee # 25 27 0 0 0 242.49 [-5.99]
26 Employee # 26 20 0 0 0 193.48 [-3.83]
27 Employee # 27 11 0 0 0 191.05 [-2.30]
28 [29] Employee # 28 7 0 0 0 190.10 [+22.60] **
29 [28] Employee # 29 20 0 0 0 187.65 [-3.62]
30 [42] Employee # 30 2 0 0 1 170.57 [+112.02] **

31 [30] Employee # 31 34 0 0 0 159.26 [-7.13]
32 [31] Employee # 32 18 0 0 0 149.12 [-2.83]
33 [32] Employee # 33 11 0 0 0 117.56 [-1.74]
34 [33] Employee # 34 26 0 0 0 109.85 [-4.97]
35 [34] Employee # 35 14 0 0 0 105.06 [-2.09]
36 Employee # 36 7 0 0 0 89.43 [-0.93]
37 [35] Employee # 37 16 0 0 0 88.62 [-2.45]
38 [37] Employee # 38 14 0 0 0 82.81 [-2.37]
39 Employee # 39 7 0 0 0 79.14 [-0.90]
40 [38] Employee # 40 26 0 0 0 77.55 [-5.59]

41 [44] Employee # 41 3 0 0 0 75.12 [+23.91] **
42 [40] Employee # 42 17 0 0 0 69.32 [-3.70]
43 [41] Employee # 43 4 0 0 0 59.06 [-1.03]
44 [43] Employee # 44 7 0 0 0 54.41 [-1.30]
45 [52] Employee # 45 5 0 0 0 49.45 [+23.90] **
46 [53] Employee # 46 2 0 0 0 47.90 [+29.80] **
47 [47] Employee # 47 4 0 0 0 46.90 [-0.57]
48 [46] Employee # 48 5 0 0 0 46.41 [-1.13]
49 [45] Employee # 49 16 0 0 0 45.60 [-2.45]
50 [48] Employee # 50 3 0 0 0 43.61 [-0.92]

51 [50] Employee # 51 14 0 0 0 37.48 [-2.35]
52 [49] Employee # 52 9 0 2 2 36.54 [-6.49]
53 [51] Employee # 53 20 0 0 0 30.32 [-2.99]
54 Employee # 54 2 0 0 0 20.42 [+2.50] **
55 Employee # 55 10 0 0 0 13.80 [-3.07]
56 Employee # 56 3 0 0 0 5.37 [-0.61]
57 Employee # 57 6 0 0 0 3.78 [-1.06]
58 Employee # 58 6 0 0 0 2.35 [-0.98]



The data I want is the points.

So for:
Employee #01: 6218.50
Employee #02: 5231.37
Etc.


Is there a good formula to extract this information?

Thank you for your help,

Art.

XP, Office 2003.
Click to expand...
 
J

JE McGimpsey

Oe way:

=MID(TRIM(LEFT(TRIM(A1), FIND("[", TRIM(A1)) - 1)), FIND("^",
SUBSTITUTE(TRIM(A1), " ", "^", 8)) + 1, 255)
 
J

JE McGimpsey

This is a bit better:

=MID(TRIM(LEFT(TRIM(A1),FIND("[",MID(TRIM(A1),20,255))+18)),
FIND("^",SUBSTITUTE(TRIM(A1)," ","^",8+ISNUMBER(FIND("[",LEFT(A1,
20)))))+1,255)

JE McGimpsey said:
Disregard - I didn't notice the extra [ in lines 7/8, etc.

JE McGimpsey said:
Oe way:

=MID(TRIM(LEFT(TRIM(A1), FIND("[", TRIM(A1)) - 1)), FIND("^",
SUBSTITUTE(TRIM(A1), " ", "^", 8)) + 1, 255)
Click to expand...
Click to expand...
 
B

Bob Phillips

I took that to be a notation that the OP added John, so I ignored it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

JE McGimpsey said:
Disregard - I didn't notice the extra [ in lines 7/8, etc.

JE McGimpsey said:
Oe way:

=MID(TRIM(LEFT(TRIM(A1), FIND("[", TRIM(A1)) - 1)), FIND("^",
SUBSTITUTE(TRIM(A1), " ", "^", 8)) + 1, 255)
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

The data I want is the points.

So for:
Employee #01: 6218.50
Employee #02: 5231.37
Etc.


Is there a good formula to extract this information?

Thank you for your help,

Art.
Click to expand...


In addition to what Bob and JE have posted, you could, alternatively, download
and install Longre's free morefunc.xll add-in from http://xcell05.free.fr and
use this regular expression formula:

=REGEX.MID(A3,"-?(\d+(\.\d*)?|(\.\d+))",-2)

The expression, "-?(\d+(\.\d*)?|(\.\d+))" matches any number (floating point)
and the -2 at the end indicates to return the next to last number.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return a value from one column using an array and a single row 1
VBA for summing buys and sells 0
Re posting - Transpose employee data from Columns to Rows 0
15 minute data to daily data 1
Transpose 4 Columns data to Rows for Employee Time In and Time Out Schedule 0
Make Y-axis values correspond to x- axis value 1
<<Extracting the desired data with an formula>> 10
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5

Top