Formula to Extract Certain Text

Hi,
Would you please help in providing a formula to extract the text as shown in column (B)
Thanks.

This will work on your sample data:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,100),"(",REPT(" ",99)),97))

If your real data differs, then it may not work, but we can't provide a solution for what you haven't told us.

Let us know, one way or the other, please.

Bassem said:

Hi,
Would you please help in providing a formula to extract the text as shown in column (B)
Thanks.

	A	B
1	Doe, John (511)	John
2	Mark, Alice (534)	Alice
3	Paul, Sarah (694)	Sarah
4	Ramsey Hanks, Mary (752)	Mary

Click to expand...

Being a far more lazier version of AliGW, take a look at flash fill (not to be confused with"Flash Dance" which I believe was a 1990's Movie...) it is exceedingly useful Excel addition (found in the Data Tab). Make sure the flash fill column is immediately adjacent to the last column of the table or data block, fill out, normally 2 items and the algorithm will pull out the desired data. So in your instance you would start the fill data column right next to your column A, type John and Alice on both rows and then click flash fill (or Control + E will do it). Think of Flash Fill as a form of power Query Lite.

AliGW said:

This will work on your sample data:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,100),"(",REPT(" ",99)),97))

If your real data differs, then it may not work, but we can't provide a solution for what you haven't told us.

Let us know, one way or the other, please.

Click to expand...

Thank you very much Ali, it works.

Ed Kelly said:

Being a far more lazier version of AliGW, take a look at flash fill (not to be confused with"Flash Dance" which I believe was a 1990's Movie...) it is exceedingly useful Excel addition (found in the Data Tab). Make sure the flash fill column is immediately adjacent to the last column of the table or data block, fill out, normally 2 items and the algorithm will pull out the desired data. So in your instance you would start the fill data column right next to your column A, type John and Alice on both rows and then click flash fill (or Control + E will do it). Think of Flash Fill as a form of power Query Lite.

Click to expand...

Thank you very much Ed, I will check this as well.

Glad to have helped!

Much easier to understand next to flash fill
You can use below formula
=MID(A1,SEARCH(",",A1)+2,(SEARCH("(",A1)-1)-(SEARCH(",",A1)+2))