A001 instead of P41_A001_2G

[sanlen]

Hi,

I have code numbers formatted in mess up way. ie. P41_A001_2G, P41_B23_2G,.....
i want to use if condition to return value base on the code number. However, i do not want to type the whole P41_A001_2G in my cell, and just want to use A001 or B23 instead.

=IF(C2=A3,"Yes","No")

How to trim a way P41_ and _2G and using only 3 characters in the middle as condition to return Yes or No?

[Marcol]

Not to sure what you are trying to do, however try this

=IF(C3=MID(A3,FIND("_",A3,1)+1,4),"Yes","No")

[DonkeyOte]

x-posted: http://www.mrexcel.com/forum/showthread.php?t=495732

[Marcol]

Hi Don,

Impressive presentation on that MrExcel link!

Can we use "Excel Jeanie Html" for a forum / messageboard?

It would, in my opinion, clarify a considerable number of posts and replies

[arthurbr]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

[sanlen]

Dear All,

My apology for this inconvenience. I did not mean it. I was so curious with the problems that i have had that why i cross-post, however, this is not an excuse.

I am so sorry for breaching the rules, and commit not to let this happen again.

Once again, thanks you very much to all people here on the forum that have contributed their experience and knowledge.

Best Regards,

[Marcol]

Hi sanlen

It is prefered that you don't cross-post

If you do post your question on other forums, just put a link to each of them with your thread.

Having seen the reply on MrExcel, I have updated the demo work book with both solutions.

Hope this helps

[DonkeyOte]

Can we use "Excel Jeanie Html" for a forum / messageboard?

Excel Jeanie does not work on this board unfortunately (at least not when I first tried it)

[sanlen]

Hi,

I am trying with the below formula to settle my problems, however, i always get #N/A.

{=INDEX('Site Detail'!$A$11:$A$500,MATCH(1,('Site Detail'!$B$11:$B$500<>"")*('Site Detail'!$B$11:$B$500=MID(K3,FIND("_",K3,1)+1,6)),0))}

I would like to enter KCN003 instead of A41_KCN003_2G1i.

Please kindly advice.

[Marcol]

If you have tried both of the solutions offered in the workbook in Post #7, and neither work for you, and that is why you are trying another formula that doesn't work for either you or me.

You would do best to post a sample workbook showing your data types and sheet/workbook layout/structure.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

[sanlen]

Hi,

Thanks you very much for your reply.

Here, i would like to attach a workbook which exactly same format as the workbook that i am working on.

[Marcol]

Try this

Sheet
B8

Please Login or Register  to view this content.

Drag/Fill Down
F8

Please Login or Register  to view this content.

Drag/Fill Down


Hope this helps

[sanlen]

Hi,

I am trying to find the option that i can mark this as SOLVED, however, i cannot figure out where it is. Please advice.

Thanks you very much for all solution that have been giving me.

Best Regards,

[Marcol]

Hi sanlen

Glad that your problem is solved.

I am trying to find the option that i can mark this as SOLVED, however, i cannot figure out where it is.

Click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.