How to extract capitalize letter text at certain position

I have only 2 columns, the first column DESCRIPTION has a long delimited data in it. While the second column AMOUNT is text with DB/CR at the end of the text (such as 12,000.00 DB).

I download bank transaction history and would like to extract the name of customers which is located at the end of the Description line. Some lines have one or two words preceding the names of the customer. How can I extract the all capitalize Names.

I also need to extract the amount transferred from text and convert it into numbers.

DESCRIPTION AMOUNT
TRSF E-BANKING DB 2701/FTSCY/WS95051 12000.00 JAMES BOND 12,000.00 DB
TRSF E-BANKING CR 2701/FTSCY/WS95051 10000.00 deposit CHRISTINE 10,000.00 CR
TRSF E-BANKING CR 2701/FTSCY/WS95051 25025.00 AMANDA B GREEN 25,025.00 CR
TRSF E-BANKING CR 2701/FTSCY/WS95051 5000.00 msn1888 JOSH BROWN 5,000.00 CR
TRSF E-BANKING CR 2701/FTSCY/WS95051 1000.00 topup CHRISTINE 1,000.00 CR

Result wanted:

CUSTOMER DEBIT CREDIT
JAMES BOND 12,000
CHRISTINE 10,000
AMANDA B GREEN 25,025
JOSH BROWN 5,000
CHRISTINE 1,000
TOTAL 12,000 41,025

Please find the attached file. Thanks Book1.xlsx

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

Hi harmin and welcome to the forum,

To get the names out:

Formula:

Please Login or Register  to view this content.

to get debit:

Formula:

Please Login or Register  to view this content.

to get credit:

Formula:

Please Login or Register  to view this content.

Sorry harimin. Here is a new formula that should give the results you want.

Formula:

Please Login or Register  to view this content.

v	A	B	C
10	CUSTOMER	DEBIT	CREDIT
11	JAMES BOND	12,000
12	CHRISTINE		10,000
13	AMANDA B GREEN		25,025
14	JOSH BROWN		5,000
15	CHRISTINE		1,000
16		12,000	41,025

Please find atttached real layout from the bank. Thank. Extract Names.xlsx

Agreed with jindon. There is no way to distinguish names from some other words in CAPS. See attached file with results in Col F. Any solution whether by VBA or formula cannot be 100% accurate.

This is as close as I can come. Array entered.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

You're welcome. Glad to help and thanks for the feedback.