Numerical expressions of multiply numbers embedded in text string

[wraithu]

Hi,

Is there a one formula that could take the numbers from A column ( i. e. 1 x 28, 10 x 100 etc.) ignoring any text description, multiply by each other and express the result numerically.

In example:

1 x 100 mL Bottle - result 100
100 x 1000 Single Use Unit Pack - result 100000
12 x 300 Pre-Filled Syringe Pack - result 3600

Spreadsheet attached.

[Ron Coderre]

Using your posted workbook
this regular formula multiplies the first number in your example cells times the second number

Please Login or Register  to view this content.

Example:
With A2: 12 x 300 Pre-Filled Syringe Pack
that formula returns: 3600


Is that something you can work with?

[Richard Buttrey]

Hi,

Not pretty and there may be a more elegant way but on a Sunday afternoon with a barbecue beckoning try

Please Login or Register  to view this content.

Obviously that can be 'simplified' by using a helper column B

Please Login or Register  to view this content.

and then in column C

Please Login or Register  to view this content.

[wraithu]

Using your posted workbook
this regular formula multiplies the first number in your example cells times the second number

Please Login or Register  to view this content.

Example:
With A2: 12 x 300 Pre-Filled Syringe Pack
that formula returns: 3600


Is that something you can work with?

That looks great and i think solves the problem. The only ones that weren't succesfully expressed are:

1 x 13.5 mL Bottle
1 x 2.5 mL Bottle
1 x 3.5 g Pack

To work out those halves will probably require a different approach. I that right?

[martindwilson]

so if there is a volume or weight it should just be 1 ? or however many there are?

[wraithu]

so if there is a volume or weight it should just be 1 ? or however many there are?

The volume or weight description is not really relevant. I need to focus on getting the correct numbers and results.

If:
1 x 13.5 mL Bottle result 13.5
2 x 13.5 mL Bottle result 27

1 x 2.5 mL Bottle result 2.5
2 x 2.5 mL Bottle result 5

[martindwilson]

works for me

	A	B	C	D
1
2	1 x 13.5 mL Bottle	13.5	Bottle result 13.5	b2 =LEFT(A2,FIND(" ",A2)-1)*TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)*2,LEN(A2)))
3	2 x 13.5 mL Bottle	27	Bottle result 27
4	1 x 2.5 mL Bottle	2.5	Bottle result 2.5
5	2 x 2.5 mL Bottle	5	Bottle result 5

[Ron Coderre]

I'm not understanding.
Those values calculate correctly with the formula I posted. Perhaps your number format is not displaying enough decimal places.

[wraithu]

I'm not understanding.
Those values calculate correctly with the formula I posted. Perhaps your number format is not displaying enough decimal places.

I get the #VALUE! error on these. I've also set the number format to display more than 2 decimal places so it should work. What's going on?

[martindwilson]

here is your workbook with formula applied

[wraithu]

here is your workbook with formula applied

When I open the file I can see that the output is fine but after enabling editing it goes back to the #VALUE! error. It's my Excels fault but WHY?!??!?

[Vikas_Gautam]

Have a look..
Ist Alternative..

Please Login or Register  to view this content.

2nd Alternative

Please Login or Register  to view this content.

check the attached file..

[boopathiraja]

Try

Please Login or Register  to view this content.

[wraithu]

Have a look..
Ist Alternative..

Please Login or Register  to view this content.

2nd Alternative

Please Login or Register  to view this content.

check the attached file..

Those formulas don't multiply the numbers.
In Example:
If

2 x 50 g Pack

the result I'm looking for is 100.

The formula:
=LEFT(A2,FIND(" ",A2)-1)*TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)*2,LEN(A2)))

Does it job but for some reason my excel on fields
1 x 13.5 mL Bottle
1 x 2.5 mL Bottle
1 x 3.5 g Pack

Shows the #VALUE! error which. It must something small in the excel set up but I can't find it.

[wraithu]

Try

Please Login or Register  to view this content.

Again, works for all fields except
1 x 13.5 mL Bottle
1 x 2.5 mL Bottle
1 x 3.5 g Pack
And showing the #VALUE! error.

[martindwilson]

perhaps your original hasn't got spaces (char 32) but some other space and trim isn't removing it
or there are more spaces than you have shown
try adding a few more trims()!!
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)*TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))*2,LEN(TRIM(A2))))

[wraithu]

perhaps your original hasn't got spaces (char 32) but some other space and trim isn't removing it
or there are more spaces than you have shown
try adding a few more trims()!!
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)*TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))*2,LEN(TRIM(A2))))

Still the same
error.gif

[Ron Coderre]

Evidently, your formulas use semicolons, instead of commas, in formulas.
What are your region settings and, more importantly, how do periods work in your region settings?

[wraithu]

Evidently, your formulas use semicolons, instead of commas, in formulas.
What are your region settings and, more importantly, how do periods work in your region settings?

Ron

That's what it was. My windows regional settings where wrong. The decimal separator was set as , and that's why excel couldn't catch 2.5 number.

Everything is fine now. Your original formula works perfectly.

Thank you very much for your help as well as other other guys -thank you for your effort!

[newdoverman]

This seems to work ok with your supplied data.

Please Login or Register  to view this content.

[Ron Coderre]

Glad you got something you can use!

[newdoverman]

The shortest that I could reduce the formula to is

Please Login or Register  to view this content.