Extracting specific part of the text string to another column

Hi Excel Experts,

Please advise me a formula that will help me bring the desired result in the column B from the available data in Column A as shown below:

Data in A column:
A1 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS A 02/19/98 701-483-3456 ABC
A2 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456 ABCD
A3 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS TC 02/19/98 701-483-3456 ABCDE
A4 = 3AB LT00022788 TH0000243428 JACK,LUC A 02/19/98 701-483-3456 701-483-3456 Andy,Pat
A5 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456 ABCDEF
A6 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS TC 02/19/98 701-483-3456 701-483-3456 ABCDEFG
A7 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS C 02/19/98 ABCDEFGH
A8 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS IF 02/19/98 Andy,Patricia J ABCDEFGHI
A9 = 3AB LT00022788 TH0000243428 STEINER,CORBAN PAUL G ABC2
A10 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456 ABCD2

Desired Result in B column
B1 = ABC
B2 = ABCD
B3 = ABCDE
B4 =
B5 = ABCDEF
B6 = ABCDEFG
B7 = ABCDEFGH
B8 = ABCDEFGHI
B9 = ABC2
B10 = ABCD2

Thanks in advance!

Hi Sanju,

Please Login or Register  to view this content.

Try this formula if this could help you

Thanks
Dinesh
Please click * below my name if this helped ypu

dinesh, I think ABC is just example so you can not search it.

Here, use this formula:

Formula:

Please Login or Register  to view this content.

As dilipandey noted, if there is a comma in last word does it need to return blank? If so, use:

Formula:

Please Login or Register  to view this content.

I am very sorry, my data mislead with the last capital alpha characters ABC being similar throughout. Actually this can be any random alphanumeric like USA, BSA2 etc.,

I think the only catch would be that these characters in the end of the string are all in caps and at times followed by a number. Also on few instances there is no capital letters in the end wherein the resultant needs to be blank.

Please advise and so sorry again. I will be more detailed in future. Thanks.

I tried but the formula provided in #6 didn't work, it shows #N/A. Please advise.

The formula provided in #10 worked. Thank you so much for all your support and everyone's time.

Originally Posted by ssanjju

I tried but the formula provided in #6 didn't work, it shows #N/A. Please advise.

Now #10 works, but anyway, #6 might return error because you hit enter. And you must comfirm formula with ctrl+shift+enter

But that would return blank for

Please Login or Register  to view this content.

and according to post #1 should return ABC2.

Here, try this. It check last characeter (must not be Uppercase if you want blank).

Formula:

Please Login or Register  to view this content.

I am very sorry, there is a glitch. While applying the formula to the huge data with me, I realized that when the string just stops with the phone # such as given below.

A1 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456

the result is the phone# whereas desired is a blank if there is no Capital alphanumeric value in the end of the string. Please help.

Also the end part of the string can be all capital alpha characters or can be capital alphanumeric value. But if in case these are not available as provided by me above the result should be a blank instead of the phone#. Please provide your as usual support. Thanks in advance.

Try this:

Formula:

Please Login or Register  to view this content.

Comfirmed with ctrl+shift+enter and not just enter

The formula in #17 worked like a charm! thank you so much Zbor for all your patience and time.