Remove #VALUE!

Hi, welcome to the forum

1. You are not using those functions correctly
- you need to specify how many characters you want for the left/right
2. Any time you use LEFT/MID/RIGHT, it always returns a text answer, not a number

so maybe something like this...
=LEFT(L4,3)*3+RIGHT(L4,3)

ASSUMING that L4 contains a number??

Hi. The cell L4 in this case will always have numbers in it such as 3.2 for example. What I need it to do it the following (3x3)+(2)= 11. I am using the formula =((LEFT(L4)*3)+RIGHT(L4)). My cells contain only numbers from 1.1, 1.2,1.3, 2.1, 2.3, 2.4 up to 8.3. My problem is that some cells contain a Z rather than a number so =((LEFT(L4)*3)+RIGHT(L4)) is returning #VALUE! which is messing up a subsequent pivot table.

OK, I understand.

Try this...
=IFERROR(LEFT(L4,1)*3+RIGHT(L4,1),"")

I believe it is a good habit to get into, to specify how many characters you want with this. Not using the ,1 in this case, is not wrong, I just prefer to add it

Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile Windows 10, but that is the operating system, not the Office version

as with most things excel, there are a number of ways to do the same thing. Here is another way to do that...

=iferror(INT(L4)*3+MOD(L4,1)*10,"")