Name Range with starting and ending characters

PrashanthKumar123

New Member
Joined
May 1, 2021
Messages
29
I have a name range with a list of keywords. I have a search/find formula that is able to extract these keywords from a text string.

Existing name range:
paytmqr, okbizaxis

Input:
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI
IMPS OUTWARD ORG UPI To XXXXXX8231@okbizaxis,REF NO - 025718167935, UPI

However, I need to add another keyword which is unique from the rest. This always starts with q and ends with @YBL, but can have any alphanumerics in between.

This way I will be able to extract this keyword from inputs such as below.

IMPS OUTWARD ORG UPI To qXXXX1411@ybl,REF NO - 025609788188, UPI

Welcome formula (or VBA) suggestions. TIA!

I'm using Excel 2016.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
513
Something like the following?

SEARCH("q*@YBL",StringToSearch)
 
Solution

PrashanthKumar123

New Member
Joined
May 1, 2021
Messages
29
Thanks much, very helpful! I included q*ybl to my existing name range and my pre-written Search/Find function worked.
 

PrashanthKumar123

New Member
Joined
May 1, 2021
Messages
29
On a related note, I was wondering if I can get feedback on my next step. Presently, I am working with a bulky "nested if" but concerned once I have to add more keywords in the future; hence wonder if there's a better way to do it.

My input sample rows as below-
IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI
PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: 5125XXX2XXXX4857 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI

My keywords in a name-range are as below.
IMPS OUTWARD
PURCHASE SUBJECT

I use a search function to tell if these keywords are present in the input text row.

Based on the whether the output is "IMPS OUTWARD" or "PURCHASE SUBJECT", I want to execute a different formula to further extract text strings from the input row. I am using nested if presently, but will like to explore better ways.

thanks in advance!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,821
Messages
5,661,129
Members
418,616
Latest member
Tony P

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
Top