I need a formula that returns a value based on data like this:
Here’s a graphical explanation of what I’m supposed to get out of the data:
If the data looked like this:
then it would be very easy, for example with:
=IF(A:A="","",LEN(A:A*LEFT(B:B,FIND(" ",B:B)-1))-FIND(".",A:A*LEFT(B:B,FIND(" ",B:B)-1)))
But sadly the data isn’t organized like this. Some cells in column B contain multiple entries, and the value from column A is used to multiply the number in each line within that cell.
I’m not sure how to approach it and while I have access to Excel functions up to around 2021, it’s best if I don’t add macros (XLSB files) because it would make the file look suspicious.
Here’s some extra information:
- Number of input rows is unknown, so it should be either 1 formula without a separate column for calculations, or the column for calculations should spill to match the number of input rows provided.
- 1st column always contains just 1 number per cell.
- In 2nd column number always comes first and is separated from the text after it with exactly 1 space.
- Number of decimal places can be easily calculated, for example this way:
LEN(A1)-FIND(".",A1).
