Using MID function and got wrong results in some cells

[ValentinaErhan]

Hello,
My name is Valentina and I need some help with MID function in excel. I am not an expert at excel, but I am trying to learn as much as possible and use the functions correctly.
I used the MID function in a column with 11 cells, the problem is that 3 cells from 11 has returned incomplete data or extra data. Those cells has wrong data.
Can you tell me why the others extracted the data that I needed (the correct data) and those 3 hasn't? I will upload the excel sheet with my issue and you can see there the function that I used and the errors from those cells - (I colored those with red so you can see exactly what I am talking about).
Thank you in advance for your help and looking forward for a solution.

[etaf]

the length changes and you are using the length - find x which should be
=MID(B2,FIND("M",B2),FIND("x",B2)-FIND("M",B2))
where X is from the Start of the string NOT the end of the string
As you are looking for the difference in characters between the M and the x
NOT the X position based on the length of the string

You can see why its 3 characters in the columns i have broken the formula down
I always break the parts out into separate cells if things don't appear to be working correctly

[Glenn Kennedy]

Another way:

C2
=LEFT(MID(B2,SEARCH(" ",B2)+1,255),SEARCH("x",MID(B2,SEARCH(" ",B2)+1,255))-1)

D2
=--RIGHT(B2,LEN(B2)-FIND("x",B2))

E2:
=INDEX(Sheet2!$C$2:$C$61,INDEX(MATCH(1,(Sheet2!$A$2:$A$61=C2)*(Sheet2!$B$2:$B$61=D2),0),0))

Since you're in Romania, you may need ; instead of ,

[ValentinaErhan]

Thank you so much for your help!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[bebo021999]

Alternative:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(B2,"M",REPT(" ",100)&"M"),"x",REPT(" ",100)),100,100))
=IFERROR(RIGHT(B2,3)+0,RIGHT(B2,2)+0)

[Glenn Kennedy]

I think you will run into problems....

If there is an M or an X, or an extra space in the brand Name

Magnetix Skrue M8x120

may cause problems. If so, post REPRESENTATIVE raw data.

[ValentinaErhan]

Hi again,
I have exactly the problems you said., but now it's different because of Torx in the data - "Unb.skr. Torx M5x8" it doesn't select M5 or 8.