0

I am trying to use formula to multiply two numbers to the left. The problem is that in one of the left cells i have letter E, or euro sign.

When there is letter, or euro sign, formula is not working correctly. Is there formula which would ignore letter? 

=Product(LEFT)
Improve this question
2
  • This is for Word or Excel?
    – Karan
    Jun 15, 2015 at 9:00
  • it is for word, i should probably delete tag. thanks
    – ivanz
    Jun 15, 2015 at 10:23

2 Answers 2

Reset to default
2

It doesn't look like Word has formulas for modifying text, only numbers. You can alter the data using Excel and copy the results into the Word table. In the long term, it's probably better to use Excel since Word is very limited for working with this sort of data. Using Excel:

It's probably best to store the data values without the Euro sign, and instead format the numbers as currency. That way, Excel will know that they're really just numbers.

To remove the first letter of a string, you can use RIGHT(<Cell>, 1) or REPLACE(<Cell>,1,1,""). To remove all non-numeric characters is a bit harder. For that you must use an extension, some VBA code, or a more impressive expression like this. To convert to a number, you can use VALUE(<cell>).

So, you could use a new column beside the cell with VALUE(RIGHT(<Cell>, 1)) to convert them to numbers, and then use PRODUCT on the new cell.

To go the other way, convert a number to text, you can use the TEXT function.

Edit:

To make using Excel with Word easier you can embed a spreadsheet into the document by selecting Insert > Table > Excel Spreadsheet (or choose Insert > Object > Excel Worksheet).

Since it's part of Excel you probably still need Excel installed to edit it, and may or may not need it to view the document. If you're a little crazy you can then insert a Word document into one of the cells of the spreadsheet.

Improve this answer
6
  • This works on Excell, would it work on MS Word also?
    – ivanz
    Jun 15, 2015 at 10:26
  • Oh thanks, I didn't know Word had this formula functionality. If you can't do it in Word, you can always do it in Excel and copy the result into the Word table.
    – user1151080
    Jun 16, 2015 at 8:09
  • if i copy it, i would lose formulas, right?
    – ivanz
    Jun 18, 2015 at 13:52
  • You'd probably need to copy the result as plain text.
    – user1151080
    Jun 21, 2015 at 10:33
  • My apologies, it looks like Word has a feature to link the table with the Excel sheet file when you copy it. So you can do the equations in Excel and save the file, preferably alongside the document so you can find it, then when you copy and paste the table in word, choose the option to link it from the little Paste Options pop-up (or press Ctrl then F).
    – user1151080
    Jul 2, 2015 at 4:33
1

You can use QUOTE and SEQ fields to create relative formulas, instead of =PRODUCT(LEFT).

This formula will calculate with $ or euro symbols, but not if text is part of number.

The workaround for text in front of number is to insert a space after the letter before the number (eg A 12). To make that space almost invisible, change its font size to 1pt (and it will look like A12, but Word can now identify the separate number and use it in the calculation).

Microsoft word `product(left)` for all numeric cells

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.