Complex stringing formula help PLEASE!

[YMUNSHI]

Hi again everyone, I wanted to ask if you could please help me on this case. I am using Microsoft Office RT (on Nokia 2520). But I would welcome a solution for Office 2010 / Office 2007 if a solution for Office RT is not practicable, as I also run these on my PC/Laptop.

My problem is that I have input the units of medicine strengths in a column, as follows:

A
1 20mg
2 2.25g
3 200mcg/600mcg
4 500mg/400unit
5 8mg/500mg
6 300/50
7 0.005%
8 10%/1%
9 1%/1%
10 10mg
11 2.5mg/5mg


The problem is, I need to use a formula that captures certain parts of these medicine strengths as a string according to the following rules:

1. If there is a number, use first three characters, but not including units or %;
2. If there is a number, with a decimal place, use the first three characters but not including the units or % (e.g. 3.529mg = "3.5"; 32.12mg = "31.");
3. If there is a figure more than 3 figures, use the first three figures, but not including units or %;
4. If there is a fraction, use the figure after the slash, up to a maximum of three characters, but not including units or %;
5. If there is a fraction such that it indicates strength per specific unit (e.g. 5mg/2ml), use the figure before the slash as the strength, up to a maximum of three figures, but not including units or %;
6. If there is a percentage, use the first three characters including any decimals (e.g. 0.005% = 0.0) but NOT including the % sign, but not including units or %;
7. If the strength indicates units as strength (e.g. 400units), do not input, but not including units or %.

So we should get something along the lines of in column B (sorry the layout is not exactly perfect):
AB
1 20mg 20
2 2.25g 2.2
3 200mcg/600mcg 600
4 500mg/400unit 400
5 8mg/500mg 500
6 300/50 50
7 0.005% 0.0
8 10%/1% 1
9 1%/1% 1
10 10mg 10
11 2.5mg/5mg 5

Can a formula be created to effect these rulings?

Any help would be greatly appreciated!!

Thanks again,

- Yousuf.

[FlameRetired]

Mostly I applied brute force.

Please Login or Register  to view this content.

[FlameRetired]

Here's a better one.

Please Login or Register  to view this content.

[YMUNSHI]

FlameRetired, you totally rock, this last formula you gave is absolutely fantastic, I can't tell you how impressed I am with your application of this brute force!

I'm really sorry, but there were four other rules I forgot to mention, I'd be really grateful if you could help incorporate these.

Basically:

1. If the strength of the medicine is measured as a fraction, but no units have been specified, do NOT input a strength, e.g. if the strength states "3/400", do not input a strength - the common units used across the spreadsheet would ideally be located in the range Worktab 2, column A between rows 1 and 10 (the list may be too exhaustive to include in a formula otherwise the formula could go on for 12 lines!)';
2. If the strength of the medicine is measured as a fraction, but no units have been specified, and the medicine name begins with "Co-", input the first three figures from the second part of the fraction, e.g. if the prescription given is Co-Codamol, and the strength given is 50/4500 (no particular units specified), the string is 450;
3. If the strength of the medicine is measured in "units" only, e.g. "400units", do not input a strength;
4. If the strength of the medicine is a fraction with "units" on both sides, e.g. 500units/200units, 3units/4units, do NOT input any figures.

For the third rule here, I was thinking of compiling a list of forbidden strength units into Worktab 2 Column B Rows 1 to 10 as we could just link the formula to this range rather than incorporating various strength units into the formula itself. But then I thought that we'd run into problems, especially if we say that "units" is not allowed knowing that the combination "x mg / y units" is allowed - if that makes sense hopefully.

For Rule 2, let us assume the name of prescriptions is in column C, and column B is the result of the formula from the data in column A:

A B C
1 5/400 400 Co-Amilofruse
2 3/500 Methadrone
3 400/30 30 Co-Codamol
4 3/40 40 Co-Dydramol
5 4/200 Phenorbital
6 40/2 2 Co-Amoxiclav


Sorry, I know this is going to sound awful, but please could you help me incorporate these four aspects into this brilliant formula? Sorry this part of the string is a right killer!

[YMUNSHI]

Problem.jpgSorry FlameRetired, I've just noticed a problem with the formula in the image attached (in 'STRING 2' column).

Basically for the Fluticasone Spray, the strength is 0.05%, and the required string should be "0.0". But the result is coming up as lone 0.

Can this be corrected, or is there a problem maybe with the cell formatting (i.e. set to number, general, etc.).

Thanks again :-)

[FlameRetired]

Thank you for the feedback.

@

Basically for the Fluticasone Spray, the strength is 0.05%, and the required string should be "0.0". But the result is coming up as lone 0.
Can this be corrected, or is there a problem maybe with the cell formatting (i.e. set to number, general, etc.).

As the formula stands it generates number values and yes the 0 that occurs instead of 0.0 presents a value / formatting issue. In order to get 0.0 treat that item as a lone exception directing the formula to return a text representation of 0.0 ....... or adjust the formula to return all items as text.

Here are the two adjusted formulas that do one or the other.
This one treats the 0.0 as a unique item.

Please Login or Register  to view this content.

This one returns all text "numbers".

Please Login or Register  to view this content.

To convert any of those text "numbers" to their underlying value put a double unary "--" in front of any reference to them or use them in any math operation. In both cases they will revert to their numeric value. BTW my usage of the "--" is what forced 0.0 to 0. I should have caught that one. My bad.

@

For the third rule here, I was thinking of compiling a list of forbidden strength units into Worktab 2 Column B Rows 1 to 10 as we could just link the formula to this range rather than incorporating various strength units into the formula itself. But then I thought that we'd run into problems, especially if we say that "units" is not allowed knowing that the combination "x mg / y units" is allowed - if that makes sense hopefully.

On the new rules part I need to spend some time with this one. I am thinking along much the same lines you mentioned ...... some lookup tables to manage the rules on what the rules are. I will probably have a bunch of questions.

To expedite it would be very helpful to have an uploaded Excel workbook file. It saves having to retype data. Include a comprehensive list representative of all the combinations to be considered. Please do include in the adjacent column a list of desired results.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in the reply window.

[YMUNSHI]

Hi again FlameRetired

Thank you so much for replying back, I really have respect for your assistance on this problem.

I've attached an Excel file with an illustration of how everything appears, and how the rules take shape against the strengths of these medicines.

Thanks so much for this, really appreciate your help on this!!

- Yousuf.

[FlameRetired]

Examine the attached file Sheet1 column C. The cells are not required to be formatted as text....in fact that complicates. When reset as "General" the formula text functions take care of the relevant issues.

The IFERROR takes care of the N/A errors.

Edit: This also takes care of column G.

[FlameRetired]

..............I've attached an Excel file with an illustration of how everything appears, and how the rules take shape against the strengths of these medicines.

- Yousuf.

With the recent upload (post 7) I'm really no clearer than I was.

Would you upload a file that presents a comprehensive list of items relevant to all the rules? The way you presented and explained in the original post was very helpful.....what you started with and what the results are to be. Provide instruction notes pointing to each item type. Indicate how the rules apply. That could be helpful.

[YMUNSHI]

With the recent upload (post 7) I'm really no clearer than I was.

Would you upload a file that presents a comprehensive list of items relevant to all the rules? The way you presented and explained in the original post was very helpful.....what you started with and what the results are to be. Provide instruction notes pointing to each item type. Indicate how the rules apply. That could be helpful.

Hi FlameRetired,

Thanks for replying back with your solutions.

I think everything is pretty much covered in the formula you designed. Did you incorporate the common and forbidden units ffrom Sheet 2 (I know I said 'Worktab 2!' - sorry!").

I've attached a spreadsheet with the comprehensive list of all theknown medicines with the results we need from them. I've highlighted errors in red, there aren't a lot. I've also put notes in each cell as well.

Thanks again FlameRetired for your help on this. You've been totally outstanding! :-)

[FlameRetired]

The latest upload helps quite a bit. There is an item in row 271. According to the "Co-" rule

this should return a blank. Cell C271 indicates 500. Is there any chance this is a typo?

Edit: C416:C418 indicate expected results to be blank. According to rules these should be numbers. Would these be typos?

[YMUNSHI]

Hi FlameRetired,

Apologies, I got my wording wrong for C271, yes there should be no result for that cell because we don't know what the '10/500' means, and also because the drug does not begin with the prefix 'Co-'.

There is a note in the cell for C416:C418 which states that if any cell contains the symbol '-' (a hyphen) to indicate a range, the result should be a blank cell. This is because the hyphen is an indication of a range in which the specific strength is not directly specified, so we can't string it. If say a strength falls within that range (such as in A910:A930), then we can use that particular strength.

Also, for A657:A659, we can't use the '6' as a strength because this we don't know what the "200/6" means (if it has specific units of measure, such as mg, then we can use the '6'). We also cannot use the '6' because the drug name does not begin with 'Co-'.

Thanks again FlameRetired for your help, I think these are the final tweaks needed for this formula.

[FlameRetired]

Sorry for my delayed reply. I had to step away from the computer for a day or so.

@ C416:C418. Yes I see the note now.

In this upload: two helper columns in F & G. The formulas are:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

respectively.
The last formula in column I is:

Please Login or Register  to view this content.

This seems to cover everything. Let us know. File is attached.

[YMUNSHI]

Hi FlameRetired,

Just want to say thank you so much for your help on this, you've really helped me so much, I'm going to spend some time analysing the formulas you gave and see how they work. I've seen the spreadsheet, and the formulas work like a charm.

Thank you once again for your help on this !!! Can't keep saying that enough!

[FlameRetired]

I'm glad they work. Thanks for the feedback.