How do you ignore data and calculate other data in the same cell ie 21A (5.5)

Hi there I have a cell with two parts of data which looks like this 21A (5.5) now 21A is a location plot number and the (5.5) is the area in rods of the plot.

Now I need to calculate the cost of this plot by multiplying the number of rods by the cost per rods, so is there a way of just using the number in the brackets in a sum and ignoring the rest of the information, now I know I could just make another cell with the 5.5 bit in it but it makes the spread sheet too complicated and wide

Thanks in advance

Sean

Hi,

=EXP(0)*MID(A1,FIND("(",A1)+1,FIND(")",A1)-(FIND("(",A1)+1))

Regards

EXP(0)?????? surely thats just 1 or am i missing something?

"EXP(0)?????? surely thats just 1 or am i missing something?"

No - you're not missing anything. Just in a creative/pretentious mood!

No worries, but you'll need to make sure you enter this as an array-formula this time:

=SUM(--MID(B9:F9,FIND("(",B9:F9)+1,FIND(")",B9:F9)-(FIND("(",B9:F9)+1)))

Regards

Dear master Martindwilson, Good afternoon.

Of course you know that the result of the formula presented before generates a TEXT.

This EXP(0) is just one of the little tricks to transform a number written in text format in a real value.
Some people use the VALUE function or even MULTIPLY whole formula by 1. ( *1 )

Thatīs it. MS Excell gives opportunity to everyone to use imagination while solving your questions.

Have a nice day.


Regards from Brazil.

@Mazzaropi you do realise that any number to the power of 0 is 1

Did you heed my emboldened, important advice about this having to be entered as an array formula?

Regards

@martindwilson - Thatīs the intention of our creative member XOR LX.

The operation donīt modify the value BUT transform TEXT to VALUE. Goal achieved.

Array formulas are confirmed with CTRL+SHIFT+ENTER, not just the usual ENTER.

Regards

"can you download my example and upload your fix please?"

I don't understand - like I said, you just take the formula I gave you, edit it in the formula bar, and instead of pressing ENTER like you normally do when you've finished typing in a formula, you hold down CTRL+SHIFT and then press ENTER.

You'll know you've done it right if you see curly brackets {} around the formula.

At least have a go!

Regards

Ah I did the CTRL+SHIFT+ENTER and it works now, but.... the is still an error #VALUE! when there is no data in a field, some people have 1 plot some have 2 and some have 3 and some have up to 5 plots so sometimes a cell will be empty how do I make the formula handle cells with no data?

You are amazing by the way!

Thanks

Ok, I see:

=SUM(IFERROR(--MID(I8:M8,FIND("(",I8:M8)+1,FIND(")",I8:M8)-(FIND("(",I8:M8)+1)),0))

Regards

Mazzaropi you are missing the point ,for example
=MOD(1001,1000)
=1234567/1234567
=LOG10(10)
=EXP(0)
=CEILING(12/13,1)
=COMBIN(3,3)
all =1 which would you rather type? those or just 1?

@Seanie You're welcome.

@martindwilson Forgive me, but I think you're the one who's misunderstanding - I'm sure Mazzaropi is fully aware that all those equate to 1, as does my EXP(0) - and, logically-speaking, it's a little absurd to use anything else other than 1*, or 0+, etc. in these constructions, but I for one (and evidently Mazzaropi for another) don't see any great harm in occasionally using alternatives (however unnecessary and redundant they may be), provided that no great confusion or misinformation is caused to the OP (or other readers), even if that means the 'cost' of typing a few extra letters.

After all, is EXP(0)* really that much less self-explanatory than the ubiquitous double-unary?

Regards

yes!.................................................. when 1 will suffice

Haha! Ok, I can see that I'm not going to win you over on this one!

Just watch out for my future constructions!!