Ignore symbol and add it back in equation

[excelbloggs]

Hello,

I have three columns, two of which have data and one that needs to output an answer to an equation. The current setup is like this:

Space	Completion	Finished
64m²	100%	64m²
20m¹	40%	8m¹

In the Finished column, I am simply wanting to do A2 * B2 to give the result you see in the Finished column but I know the 'm²' / 'm¹' is causing issues and I simply get '#VALUE'.

I tried to use Substitute and was able to get the correct answer, but it is not dynamic as I have only defined 'm²' so I can't copy the cells down properly and it also does not append the 'm²' back to the result.

=SUBSTITUTE(A2, "m²","")*B2

This gives me the result of '64' but ideally it would output '64m²'

Does anyone have any suggestions for an equation that will essentially be able to:

1. Check whether column A contains 'm¹' or 'm²' and ignore them
2. Multiply by the value in the B column of the same row
3. Add the 'm¹' or 'm²' back to the result

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C
1	Space	Completion	Finished
2	64m²	100%	64m²
3	20m¹	40%	8m¹
Data

Cell Formulas
Range	Formula
C2:C3	C2	=LEFT(A2,LEN(A2)-2)*B2&RIGHT(A2,2)

 

[excelbloggs]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C
1	Space	Completion	Finished
2	64m²	100%	64m²
3	20m¹	40%	8m¹
Data

Cell Formulas
Range	Formula
C2:C3	C2	=LEFT(A2,LEN(A2)-2)*B2&RIGHT(A2,2)

Legendary - thank you, Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.