Forum Discussion

Joshua Gartler's avatar
Joshua Gartler
Copper Contributor
May 02, 2017

Formula help please

I have two columns of text. One column has Text A and Text B in each cell (AB) and one column has Text B. Each row has a different value for AB and B how do I write a formula so that I can get text A in a column.

 

for example a cell in Column AB has text "The quick brown fox 2861B" and Column B has text "2861B".

I need to create a third column with only Text A "The quick brown fox". 

 

I know this must be possible but I'm stumped.

 

Thanks for any help you can provide.

-Josh

Formulas & Functions
Need Help

12 Replies

Sort By
  • manoj patgar's avatar
    manoj patgar
    Copper Contributor
    May 09, 2017

    Hi Joshua Gartler,

     

    Good day,

     

    Please try to use below simple formula. If your requirement is defferent then please try to send work sheet.

    Please find attached work sheet.

     

    Regards,

    Manoj P.

  • Zachary Grotovsky's avatar
    Zachary Grotovsky
    Brass Contributor
    May 02, 2017

    Hi Joshua - this is a good situation to use the FlashFill feature. Lets say you have 10 rows in your example. Start typing "the quick brown fox" in a new column. Go to the next row, and start typing whatever is your "A" text. Once you do this a few times, Excel may recognize your pattern and fill it down for you. You can also select the cells that have this pattern and hit ctrl-E. There is a 3-minute video that explains further at this link: https://support.office.com/en-us/article/Use-AutoFill-and-Flash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464?ui=en-US&rs=en-US&ad=US 

     

    Hope this helps!

    • Joshua Gartler's avatar
      Joshua Gartler
      Copper Contributor
      May 02, 2017

      I'll give it a try. It doesnt sound like there is enough repetitive values or formats for it to work. . Each row has a different value for AB and B

      here is a sample of the column:

      Column ABColumn B
      Notre Dame Hanson, 1926  Football South Shore (collotype repro) 1499114991 
      LITTLE MISS SUNBEAM SAYS "NOT BY BREAD ALONE" SUNBEAM BAKERS 11512 11512
      ART ANCIEN DU MEXIQUE ET DU PERU - silkscreened poster 14973 14973
      Keep the Home Fires Burning, 1942 06474 06474
      Achille Mauzan - JUGUETES VOSS - original ca. 1930 Argentina  poster 06084 06084

       

      in each row I need to remove the values at the end (usually but not always numbers)

       

Resources