# Thread: Formula to Extract Certain Text

1. ## Formula to Extract Certain Text

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

2. 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.

3. Originally Posted by Bassem
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

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.

4. Originally Posted by AliGW
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.

Thank you very much Ali, it works.

5. Originally Posted by Ed Kelly
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.
Thank you very much Ed, I will check this as well.

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