169 MERCHANT BNK 1680338392DEPOSIT 324050609993 09100001137600715121
I have the following text in a cell. I am trying to export the words "merchant bnk" and Deposit so then I can do a vlookup.
=IFERROR(INDEX({"MERCHANT";"DEPOSIT"},MATCH(1,INDEX(COUNTIF(A3,{"*Merchant*";"*DEPOSIT*"}),0),0)),"") I tried this formula but it only pulled the word "merchant" out. I have 16 different words that I need to pull out of multiple line descriptions.
Any help would be greatly appreciated.
Can you give us more sanitized examples and what you want to see returned, based on the samples?
...and the list of words to find?
Welcome to the Forum jrae39.
If you will upload a sample Excel file (not pics, please) it will be simpler / easier to answer your questions. Be sure to desensitize all your data and include enough sample representative of the data you are working with.
To upload a file click Advance below. Then scroll down and click Manage Attachments. In the dialogue box click Add then browse (select your file) click upload and then click Done (lower right hand corner).
What do you want to pull out if it contains both?
Your formula will only result in MERCHANT, DEPOSIT, or "".
169 MERCHANT BNK 1680338392DEPOSIT 427165761995 09100001562675915124
169 WELLPARTNEGE 931257282A340B Pay SMCH 12113752124148815121
469 AMERICAN EXP 1134992250AXP DISCNT1260563325 09100001338808615125
469 AMERICAN EXP 1134992250COLLECTION1260563325 09100001018850815121
469 CATHOLIC HEA 747061737389AP 0400741NATA 11100002657117215121
469 MCKESSON DRU 9991000992AUTO ACH ACH02503576 09100001824441515127
MERCHANT BNK DEPOSIT
MCKESSON DRU AUTO ACH
AMERICAN EXP AXP DISCNT
AMERICAN EXP COLLECTION
CATHOLIC HEA AP
MCKESSON DRU AUTO ACH
The bottom information is what I am trying to extract out of the the information. It would be like the top stuff would be in column A and the info I am trying to extract would be in column B. This is the first time I have attempted a formula for extracing info.
Last edited by jrae39; 05-20-2015 at 08:08 AM.
Using the following User Defined Function (UDF) will get you close(r).
You can find instructions for creating a UDF on the 'net.
Please Login or Register to view this content.
The in B1, copied down...
Used on your example list returns:Please Login or Register to view this content.MERCHANT BNK DEPOSIT
WELLPARTNEGE AB Pay SMCH
AMERICAN EXP AXP DISCNT
AMERICAN EXP COLLECTION
CATHOLIC HEA AP NATA
MCKESSON DRU AUTO ACH ACH
or use SUBSTITUTE
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Row\Col 169 MERCHANT BNK 1680338392DEPOSIT 427165761995 09100001562675915124 MERCHANT BNK DEPOSIT 169 WELLPARTNEGE 931257282A340B Pay SMCH 12113752124148815121 WELLPARTNEGE AB Pay SMCH 469 AMERICAN EXP 1134992250AXP DISCNT1260563325 09100001338808615125 AMERICAN EXP AXP DISCNT 469 AMERICAN EXP 1134992250COLLECTION1260563325 09100001018850815121 AMERICAN EXP COLLECTION 469 CATHOLIC HEA 747061737389AP 0400741NATA 11100002657117215121 CATHOLIC HEA AP NATA 469 MCKESSON DRU 9991000992AUTO ACH ACH02503576 09100001824441515127 MCKESSON DRU AUTO ACH ACH
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
This doesn't work to shorten the formula from AlKey. Any idea why?
EDIT: I have used the curly brackets inside a formula before to create an array. The formula usually searches all of the values in the array. In this case the formula just takes the first value (0) and doesn't look at the other values.Please Login or Register to view this content.
Last edited by nigelbloomy; 05-20-2015 at 10:41 AM.
• Select the cell with that formula
• Press F2....Press F9
That will show you the formula results.
• Press ESC to restore the formula
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks