Formula for separating multiplication into multiplier and multiplicand

[bansche123]

Dear Forum Users,

I’d like to kindly ask for help regarding separating multiplications (that are located in column B) as text into the multiplier and multiplicand as shown in columns H and I.

Unfortunately, the multiplications must have an empty cell in-between them, I hope this isn’t much of a inconvenience.

Thank you in advance

[Glenn Kennedy]

In H2, copied across and down:

=TRIM(MID(SUBSTITUTE("x"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX($B$3:$B$17,1+2*(ROWS(H$2:H2)-1))," ",""),"zł",""),",","."),"x",REPT(" ",125)),125*COLUMNS($A:A),125))+0

again, you may need to use ;

[Rick Rothstein]

Assuming you will copy the formulas past where the last data row would output results (in anticipation of future data perhaps)...

If you don't mind zeros at the bottom of your output for blank cells after the end of your data, then you can use this formula...

Please Login or Register  to view this content.

otherwise this formula will hide those zeros at the bottom of the output columns...

Please Login or Register  to view this content.

Put which ever formula you chose in cell H2 and copy across to I2, then copy H2:I2 down as far as you think you will ever need.

[bansche123]

Thank you Sir, for your help. There is one problem. Your formulas work only for the H column, in the I column I only get zeros in every row.

[Rick Rothstein]

Who are you directing your comment to... me or Glenn?

[bansche123]

@Rick Rothstein

To you Sir.

[Rick Rothstein]

@Rick Rothstein

To you Sir.

I tested my formulas before I posted them and, to be sure, just now again... they both work fine for me filling in Columns H and I as expected. I tested my formulas against the data you posted in workbook you provided. Did you copy the formulas exactly as I posted them or did you retype them?

[bansche123]

@Rick Rothstein

Yes, I did Sir.

Maybe you could upload your file please?, because I need to translate your formula from english, maybe there I make a mistake.

[Rick Rothstein]

I would note the formula does not change... the formula in H2 should be copied across to cell I2, then copy both of those cells down. Anyway, here is the workbook. Note that I used both formulas so you could see how the blank cells after your data is treated.

[bansche123]

Anyway, here is the workbook.

Thanks a lot, now it works, apparently excel has updated some formulas addding @ in there somewhere.