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
![]()
=RIGHT(LEFT(A2,FIND("cv",A2)-1),LEN(LEFT(A2,FIND("cv",A2)-1))-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
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,![]()
=IF(FIND(" ",B3&" ")<FIND("cv",B3),MID(B3,FIND(" ",B3)+1,FIND("cv",B3)-FIND(" ",B3)-1),MID(B3,1,FIND("cv",B3)-1))
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