Extracting data from a text string:

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

 

[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)

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.

 

[JE McGimpsey]

Oe way:

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

 

[JE McGimpsey]

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

 

[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)

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

Oe way:

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

 

[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)

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

Oe way:

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

 

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

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