search cell and return search value

Guanjin Peter

Active Member
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

dafan

Well-known Member
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?

Guanjin Peter

Active Member
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:

dafan

Well-known Member
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?

Guanjin Peter

Active Member
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:

Guanjin Peter

Active Member
sorry for not being clear, for (*) I know how to do, but for * - , i aren't sure.

dafan

Well-known Member
Code:
``=LEFT(A1,LEN(A1)-FIND("-",A1))``

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

Guanjin Peter

Active Member
works awesome! thanks man!

Replies
0
Views
374
Replies
1
Views
490
Replies
3
Views
278
Replies
5
Views
222
Replies
4
Views
461

Threads
1,191,197
Messages
5,985,231
Members
439,951
Latest member
flamingogaslight

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

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