extract numbers from a cell, before a specific text

[xlepws]

Hi, I have a column which stores info about cars, like

q7 6.0 v12 tdi 500cv
s 350 bluetec 4matic 3.0 v6 258cv

I would like to extract just the number before "cv"; that would make
500
258

How can I achieve that?

Thanks for your help!

[Moo the Dog]

Try this:

If item is in cell A1...

=MID(A1,FIND("cv",A1)-3,3)

Note: The result will be a text string. If you need the result to be numeric, you could change it to this:
=MID(A1,FIND("cv",A1)-3,3)*1

- Moo

[sbehera]

try this...asuming that ur data is in A2

Please Login or Register  to view this content.

[xlepws]

Thank you. Moo's formula seems to work a little better, though it's not applicable when I have 99cv or lower
For instance, sbehera's give a "#VALUE!" error when the cell contains just "170cv"

So let's say the formula should work in the following cases:

170cv xxxx
170cv
xxxx 170cv
xxxx 170cv xxxx
50cv xxxx
50cv
xxxx 50cv
xxxx 50cv xxxx

always extracting only the number before "cv"

Thanks again for your kind help!

[Fotis1991]

In your example i see that after ALL your numbers exist ALWAYS a "cv". Is like that and in your real sheet?

[mnjofra]

Hi,
try this, assuming have data in B3...

Please Login or Register  to view this content.

Regards,

[Moo the Dog]

Short and sweet:

=IF(FIND("cv",A1)<4,LEFT(A1,2),TRIM(MID(A1,FIND("cv",A1)-3,3)))

- Moo

[xlepws]

In your example i see that after ALL your numbers exist ALWAYS a "cv". Is like that and in your real sheet?

Hi. No, because I could have a cell containing something like "q7 6.0 v12 tdi 500cv" as written in the 1st post thanks

[Moo the Dog]

Does the updated formula in my post (#7) work for you?

[xlepws]

Does the updated formula in my post (#7) work for you?

Yup, that works like a charm, I was about to write it

thanks a lot!!