Forum Discussion
Formula help please
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!
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,""))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.
- this didnt work since there are too may inconsistencies
Hi Joshua,
You formula shall work assuming 1) you have the text in column B, not numbers; 2) text from B appears only at very right of A and not repeated in the middle; and if you deduct 1 from the search result since it gives you the position where textB starts in A. Other words
=LEFT(A2, SEARCH(B2,A2,1)-1)
Another approach could be not to use Column B at all if only assume what trailing word is always separated from the rest of the string by space.
First, you calculate how many spaces are in your string compare length of it with with length of the same string with removed spaces
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
Second, you substitute the latest space in initial string by any character which definitely could be not within the text, in this case with unprintable character CHAR(160):
=SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))
next find the position of this character within the string
=FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))
and take left part of the text before that position
=LEFT(A2, FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
To avoid an error if your text doesn't have spaces at all better to check and return initial text for such case
=IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2, FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))And finally, your text in A could have trailing spaces, we need to trim them for the correct calculations, other words use TRIM(A2) instead of A2. Thus final formula will be
=IF(ISERROR(FIND(" ",TRIM(A2))),A2,LEFT(TRIM(A2), FIND(CHAR(160),SUBSTITUTE(TRIM(A2)," ",CHAR(160),LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))))
Im really close but cant quite get the formula to do what I need. I dont understand why.
using formula =LEFT(A2, SEARCH(B2,A2,1))
Column AB
Column B Result from formula Achille Mauzan - REFRESCANTE NESTLE - original 1931 Argentina poster 10415 10415 Achille Mauzan - REFRESCANTE NESTLE - original 1931 Argentina poster 1 it obviously has something to do with the [start number] but im not sure why i still have a "1" at the end of my result
