search cell and return search value

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
i did a search at the forum, I found that you are able to search for certain crtira for words. But what if it's random like above?
It'll search for the 2nd and the 3rd letter, and return the 2nd and 3rd letter. The 2nd part I dunno if it's possible, i'll leave it as an seperate question. Would prefer formula rather than vba code(so that it's easier to copy down)

RPM - 100
MRM - 110
VFG - 110
VRM - 100

Return value:
PM
RM
FG
RM

the 2nd part is the tricky part
RPM - 100
MRM - 110
VFG - 110
VRM - 100
NUH - 150

Return value:
PM
RM
FG
RM
NUH
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Why should NUH return NUH and what is this criteria based on (when to return 2 letters and when to return 3).

And, are you really doing a search or do you just want the 2nd and 3rd letter of all strings?
 
Upvote 0
Thanks 4 the relpy. I just want to 2nd part only.
That's why it's pretty tricky for me. it is 2nd and 3rd character for the 1st 4, and the last one, full (1,2,3!). That's why I assume 2nd part is impossible.


Maybe i'll put a search formula inside the if formula (that I can figure out), so if it doesn't return any of above, it'll return NUH automatically (haha...)
 
Last edited:
Upvote 0
Code:
=MID(A1,2,2)
Will return the 2nd and 3rd charactar.

Code:
=MID(A5,2,3)
Will return the 2nd, 3rd and 4th.

I'm still not clear how you know when to select 2 or when to select 3 chars?
 
Upvote 0
actually I wasn't clear also, I was comparing 2 columns. From what I see, I see it this way. Thanks!


just in case, where would i put the * in order to search before the "-" sign?
so it'll return the * value?
happy - would return happy and sad - would return sad

for example if it is in () I would put (*),
so (happy), would return happy and (sad) would return sad

would * - work?
 
Last edited:
Upvote 0
Code:
=LEFT(A1,LEN(A1)-FIND("-",A1))

Will return everything before "-" (including any spaces)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top