Excel Text formula

[LAY2022]

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
	A	B
1	DESCRIPTION	OUTPUT
2	BKOFAMERICA ATM 01/24 #000003704 WITHDRWL BOLINGBROOK BOLINGBROOK IL	BANK OF AMERICA
3	CHASE CREDIT CRD DES:EPAY ID:3917523958 INDN:JOSE L ROMAN CO ID:5760039224 WEB	CREDIT CARD
4	CHECKCARD 0101 UNLIMITED VACATIO 8779232582 65130199002000000052690 CKCD 7011 XXXXXXXXXXXX3266 XXXX XXXX XXXX 3266	UNLIMITED VACATIO
5	External transfer fee - 3 Day - 01/15/2019	BANK OF AMERICA
6	External transfer fee - Next Day - 01/07/2019	BANK OF AMERICA
7	Online Banking transfer to CHK 0963 Confirmation# 1471021507	CHK 0963
8	Online Banking transfer to CHK 7605 Confirmation# 2357046919	CHK 7605
9	TRANSFER INTEGRATED TECHNOLOG:A7A TELECOM INC Confirmation# 0486301605	A7A TELECOM INC
10	WIRE 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 11219	LEE A. PALMER III
11	BEST BUY DES:PAYMENT ID:212836450670095 INDN:CHRISTIAN ABALA CO ID:CITIGPUFDR TEL	BEST BUY
12	PRUDENTIAL DES:INS PREM ID:2L9641160019025 INDN:CHRISTIAN ABALA CO ID:9478857501 PPD	PRUDENTIAL
Sheet1

Thanks,
Lay

 

[rowebca]

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.

 

[LAY2022]

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.

 

[rowebca]

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

 

[saboh12617]

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.