Hi, I have a column which stores info about cars, like
I would like to extract just the number before "cv"; that would makeq7 6.0 v12 tdi 500cv
s 350 bluetec 4matic 3.0 v6 258cv
500
258
How can I achieve that?
Thanks for your help!
Hi, I have a column which stores info about cars, like
I would like to extract just the number before "cv"; that would makeq7 6.0 v12 tdi 500cv
s 350 bluetec 4matic 3.0 v6 258cv
500
258
How can I achieve that?
Thanks for your help!
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
Last edited by Moo the Dog; 01-10-2013 at 12:47 PM. Reason: Added note.
try this...asuming that ur data is in A2
![]()
Please Login or Register to view this content.
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:
always extracting only the number before "cv"170cv xxxx
170cv
xxxx 170cv
xxxx 170cv xxxx
50cv xxxx
50cv
xxxx 50cv
xxxx 50cv xxxx
Thanks again for your kind help!![]()
Last edited by xlepws; 01-11-2013 at 05:31 AM.
In your example i see that after ALL your numbers exist ALWAYS a "cv". Is like that and in your real sheet?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi,
try this, assuming have data in B3...
Regards,![]()
Please Login or Register to view this content.
If a post helps press star sign 4 my reputation
Short and sweet:
=IF(FIND("cv",A1)<4,LEFT(A1,2),TRIM(MID(A1,FIND("cv",A1)-3,3)))
- Moo
Does the updated formula in my post (#7) work for you?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks