Forum Discussion
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...
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 AB | Column B |
| Notre Dame Hanson, 1926 Football South Shore (collotype repro) 14991 | 14991 |
| 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)
Just use:
=TRIM(SUBSTITUTE(AB2,B2,""))
=TRIM(SUBSTITUTE(AB2,B2,""))
Jan,
I used
=LEFT(A2, SEARCH(B2,A2,1)-2)
which worked for most but not all of my data. I had to adjust about 10% of the cells manually but it still saved me a lot of time.
I got your suggestion after I did all the work but that looks like it also solved my issue. It also fixed the spacing issue I had with some of the text. Thank you.
- Kindly provide some examples of the data in which the formula did not work the way you wanted it to.
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India
- Thanks for posting this, I reread what Joshua needed and this does seem to fit his needs well.
