Excel Text formula

LAY2022

New Member
Joined
Jun 19, 2024
Messages
10
Office Version
  1. 365
Hi,

I need an excel formula to extract the string of text in column A. The output should be the same in column B.


Book7
AB
1DESCRIPTIONOUTPUT
2BKOFAMERICA ATM 01/24 #000003704 WITHDRWL BOLINGBROOK BOLINGBROOK ILBANK OF AMERICA
3CHASE CREDIT CRD DES:EPAY ID:3917523958 INDN:JOSE L ROMAN CO ID:5760039224 WEBCREDIT CARD
4CHECKCARD 0101 UNLIMITED VACATIO 8779232582 65130199002000000052690 CKCD 7011 XXXXXXXXXXXX3266 XXXX XXXX XXXX 3266UNLIMITED VACATIO
5External transfer fee - 3 Day - 01/15/2019BANK OF AMERICA
6External transfer fee - Next Day - 01/07/2019BANK OF AMERICA
7Online Banking transfer to CHK 0963 Confirmation# 1471021507CHK 0963
8Online Banking transfer to CHK 7605 Confirmation# 2357046919CHK 7605
9TRANSFER INTEGRATED TECHNOLOG:A7A TELECOM INC Confirmation# 0486301605A7A TELECOM INC
10WIRE TYPE:WIRE OUT DATE:190118 TIME:1508 ET TRN:2019011800397098 SERVICE REF:012771 BNF:LEO A. PALMER III ID:525032129 BNF BK:JPMORGAN CHASE BANK, NA ID:075000019 PMT DET:252857794 WEE KENDING 11219LEE A. PALMER III
11BEST BUY DES:PAYMENT ID:212836450670095 INDN:CHRISTIAN ABALA CO ID:CITIGPUFDR TELBEST BUY
12PRUDENTIAL DES:INS PREM ID:2L9641160019025 INDN:CHRISTIAN ABALA CO ID:9478857501 PPDPRUDENTIAL
Sheet1



Thanks,
Lay
 
First of all your data has to be the same to be possible to apply an algorithm (to extract data based on algorithm). So from "BKOFAMERICA" you want "BANK OF AMERICA"....Good luck.
 
Upvote 0
First of all your data has to be the same to be possible to apply an algorithm (to extract data based on algorithm). So from "BKOFAMERICA" you want "BANK OF AMERICA"....Good luck.
Hello,

This is the output that I am looking for.

For row 2 - I need to find the ATM and replace it with Bank of America
row 3 - Jose L Roman
row 4 - extract the text after the word CHECKCARD xxxx
row 5 & 6 - External transfer fee to Bank of America
row 7 & 8 - to extract the text CHK xxxx
row 9 - to extract A7A TELECOM INC
row 10 - to extract LEO A. PALMER III
row 11 & 12 - Best Buy & Prudential

I hope this make sense.
 
Upvote 0
It still doesn't make sense (it is more easy to do things manually than with a code). You should do this manually not with a code. Try to have same organized data and than you can apply the same algorithm, until then forget about coding.

Regards,
GB
 
Upvote 0
Hello,

@rowebca is completely right, you expect your computer to do magic. In reality you need to teach it everything.

One way i would approach your problem is to 1st make a database of all your potential outputs, as a list.
Then, for each row, you do a search of each of this database items in the "description" column, and you return the one that matches (i hope there won't be overlaps of names, otherwise you will need more logic in the code/formula).

But first you have to make this list, otherwise it's ridiculous. Even us could not do the job, looking at row 5 for example it seems that Bank of America is a default value, which you never explained.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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