Question: [![enter image description here](https://i.stack.imgur.com/Nnu6p.png)](https://i.stack.imgur.com/Nnu6p.png)

I want to make excel automatically multiply the hourly rate by the hours done by person A and B up to Z to create the column D total dollars but my data is formatted this way.

How do I make excel do this without changing my formatting and having to take out a calculator?

Answer: 
As a workaround, when entering hourly rate ($) and hourly rate ($) data, add a space " " after the first line of data.  

For example, "930 " and "3.5 ", you maybe could try this:

```
=LEFT(B2,FIND(" ",B2)-1)*LEFT(C2,FIND(" ",C2)-1)+RIGHT(B2,FIND(" ",B2)-1)*RIGHT(C2,FIND(" ",C2)-1)

```

[![enter image description here](https://i.stack.imgur.com/y0VeC.png)](https://i.stack.imgur.com/y0VeC.png)

In addition, in Excel, it is recommended to place only one data in a cell, which is extremely simple.
[![enter image description here](https://i.stack.imgur.com/GfZ8f.png)](https://i.stack.imgur.com/GfZ8f.png)

---

---

**Update：**  

No need to do anything else, try this:

```
=LEFT(B15,FIND(CHAR(10),B15)-1)*LEFT(C15,FIND(CHAR(10),C15)-1)+RIGHT(B15,FIND(CHAR(10),B15)-1)*RIGHT(C15,FIND(CHAR(10),C15)-1)

```

[![enter image description here](https://i.stack.imgur.com/g63Ji.png)](https://i.stack.imgur.com/g63Ji.png)
You can ignore column A as it's the product of columns B & C you're after.

Split those columns into an array using `FILTERXML` (an example of splitting column B):  

`=FILTERXML("<t><s>" & SUBSTITUTE(B2,CHAR(10),"</s><s>") & "</s></t>","//s")`.

You can then use `SUMPRODUCT` on the result:

```
=SUMPRODUCT(FILTERXML("<t><s>" & SUBSTITUTE(B2,CHAR(10),"</s><s>") & "</s></t>","//s"),FILTERXML("<t><s>" & SUBSTITUTE(C2,CHAR(10),"</s><s>") & "</s></t>","//s"))  

```

[![enter image description here](https://i.stack.imgur.com/BL4xV.png)](https://i.stack.imgur.com/BL4xV.png)