extract and sum

[a.hudrea]

Hello and thank you in advance for any advice that can help me with the following.
I have this "statement" :FS265602-01 : 08-10-2014 : 2; FS265603-02 : 15-10-2014 : 50; FS266546-03 : 29-10-2014 : 50;
I need to somehow extract the character after the date (2, 50, 50) and sum it up. Any ideas?
Thank you.

[bmouse]

Kinda hard to say without an example, but the first thing that comes to mind is Text To Columns to split it up. After that (if it works) its an easy SUM formula.

[XOR LX]

Hi.

Are the values to be summed always the 3rd, 6th, 9th, etc. delimited substrings in the string?

Also, are the values to be summed always immediately followed by a semi-colon? And will there ever be any other semi-colons in the string which are preceded by something other than a value to be summed?

Regards

[a.hudrea]

Values are always followed by semicolon, and what I wrote in a statement could be a single cell. "FS265602-01 : 08-10-2014 : 2; FS265603-02 : 15-10-2014 : 50; FS266546-03 : 29-10-2014 : 50; "

[bmouse]

So, can you split it up and do the calculation?

[XOR LX]

Values are always followed by semicolon, and what I wrote in a statement could be a single cell. "FS265602-01 : 08-10-2014 : 2; FS265603-02 : 15-10-2014 : 50; FS266546-03 : 29-10-2014 : 50; "

Thanks, but what about my other two questions?

Regards

[a.hudrea]

test.xlsx

This would be a good example.

[a.hudrea]

There are no other numbers before ";" except what I need to add and I am not sure what do you mean about 3rd 6th 9th.
And I'm open to any solutions so splitting would be ok. The result is all i'm after.

[XOR LX]

Hi.

Not sure I understand.

Of the entries in your attached file, only two contain dates:

FS266893-05 : 29-10-2014 : 25;
FS266544-03 : 22-10-2014 : 25;

and each of these has only one value (25) to be summed.

The rest do not contain dates, e.g.:

FS267437-03 : 100; FS267438-05 : 125;
FS267437-04 : 200;
FS267437-03 : 100; FS267438-05 : 125; FS268169-10 : 25;

so I am not sure what should be done with these.

Regards

[a.hudrea]

Those are the type of values that I have, dates are not important I still only need the value just before the semicolon. "FS266893-05 : 29-10-2014 : 25"; = 25, "FS267437-03 : 100;=100"

[XOR LX]

Perhaps this array formula**:

=SUM(IF(MID(SUBSTITUTE(A1," ",REPT(" ",10)),ROW(INDIRECT("1:999")),1)=";",0+MID(SUBSTITUTE(A1," ",REPT(" ",10)),ROW(INDIRECT("1:999"))-10,10)))

Increase the 999 suitably if you have any strings of a length greater than that value.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[a.hudrea]

Thank you so much, it works perfectly, if you have a couple of minutes to explain the formula a bit so I can understand the thinking behind it it would be awesome, if not its perfect anyways. Thank you once again and have a greeat day.

[XOR LX]

In essence it locates the position of each semi-colon within a version of the string which has first had its single spaces replaced with larger spaces.

It then returns strings located from a position ten characters before these semi-colons and of length 10 characters.

These strings will necessarily contain your desired extractions, and so are first converted to numerics, where possible (the +0 part does this), and then summed.

For example, for the string:

FS267437-03 : 100; FS267438-05 : 125;

the formula would resolve to (I won't give all 999 results, since it's not necessary here):

=SUM(IF({"F";"S";"2";"6";"7";"4";"3";"7";"-";"0";"3";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";":";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";"1";"0";"0";";";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";"F";"S";"2";"6";"7";"4";"3";"8";"-";"0";"5";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";":";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";"1";"2";"5";";";...}=";",0+{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;"FS267437-0";"S267437-03";"267437-03 ";"67437-03 ";"7437-03 ";"437-03 ";"37-03 ";"7-03 ";"-03 ";"03 ";"3 ";" ";" :";" : ";" : ";" : ";" : ";" : ";" : ";" : ";" : ";": ";" ";" 1";" 10";" 100";" 100;";" 100; ";" 100; ";" 100; ";" 100; ";" 100; ";"100; ";"00; ";"0; ";"; ";" ";" F";" FS";" FS2";" FS26";" FS267";" FS2674";" FS26743";" FS267438";" FS267438-";"FS267438-0";"S267438-05";"267438-05 ";"67438-05 ";"7438-05 ";"438-05 ";"38-05 ";"8-05 ";"-05 ";"05 ";"5 ";" ";" :";" : ";" : ";" : ";" : ";" : ";" : ";" : ";" : ";": ";" ";" 1";" 12";" 125";...}))

which is:

=SUM(IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;41705;-3;3;3;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;10;100;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;41767;-5;5;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;12;125;...}))

i.e.:

=SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;100;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;125;...})

which is 225.

Regards