Looking for formula to extract values/text ONLY from column I. Desired outcome is in column J. See attached sample.
Looking for formula to extract values/text ONLY from column I. Desired outcome is in column J. See attached sample.
You could use this in J2 and drag down but I think it would be easier to simply construct it from sheet 1 where you already have the individual data fields. Up to you though
=IFERROR(MID(I2,FIND("-",I2)+1,LEN(I2)+FIND("-",I2)+1-FIND("-",I2,FIND("-",I2)+1)),LEFT(I2,FIND("-",I2)-1))
Happy with my advice? Click on the * reputation button below
If Here!J2 should extract text from Here!I2,
Here!J2:
Formula:Please Login or Register to view this content.
Fill J2 down as far as needed.
If there are 2 or more space-dash-space substrings (-) in I2, this returns the text between the 1st and 2nd. If there's only 1 such substring in I2, this returns the text to the left of it. If there's no such string, this returns I2 as-is.
Or try:
=TRIM(MID(SUBSTITUTE(I2,"-",REPT(" ",250)),1+250*(COUNTIF(I2,"* - * - *")),250))
Phuocam: Outstanding solution. Thanks a lot.
hrlngrv: Outstanding solution. Thanks a lot.
Did my solution not work?
Crooza: Yes, Crooza. Excellent solution. Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks