0

How do I create a formula that only prints out the words ending with a dot. See below and image for examples.

7-5/8 TOWER ASSY. KIT -> ASSY.
90 DEG HYD.FITTING -> HYD.
90 DEG SPLIT FLANGE CDE. -> CDE.

Expected result:
Formula that writes out only the words ending with a .

Thanks!

2 Answers 2

3

Well it got kind of long but this seems to do the trick. It will return the word prior to the first period in the cell and the period itself.

=MID(A1,FIND(CHAR(160),SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",CHAR(160),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",""))))+1,FIND(".",A1)-FIND(CHAR(160),SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",CHAR(160),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ","")))))
3
  • I also add the formula with ";" =MID(A2;FIND(CHAR(160);SUBSTITUTE(LEFT(A2;FIND(".";A2));" ";CHAR(160);LEN(LEFT(A2;FIND(".";A2)))-LEN(SUBSTITUTE(LEFT(A2;FIND(".";A2));" ";""))))+1;FIND(".";A2)-FIND(CHAR(160);SUBSTITUTE(LEFT(A2;FIND(".";A2));" ";CHAR(160);LEN(LEFT(A2;FIND(".";A2)))-LEN(SUBSTITUTE(LEFT(A2;FIND(".";A2));" ";""))))) Mar 15, 2019 at 19:20
  • 2
    The answer formula will error when there is no dot/semicolon or no space before the dot/semicolon. This modified version of the formula for dot . guards against these errors =IFERROR(MID( A1, IFERROR(FIND( CHAR(160), SUBSTITUTE( LEFT( A1, FIND( ".", A1)), " ", CHAR(160), LEN( LEFT( A1, FIND( ".", A1))) - LEN( SUBSTITUTE( LEFT( A1, FIND( ".", A1)), " ", ""))) ),0) + 1, FIND( ".", A1) - IFERROR(FIND( CHAR(160), SUBSTITUTE( LEFT( A1, FIND( ".", A1)), " ", CHAR(160), LEN( LEFT( A1, FIND( ".",A1))) - LEN( SUBSTITUTE( LEFT( A1, FIND( ".", A1)), " ", "")))),0)),"")
    – Ted D.
    Mar 15, 2019 at 19:55
  • (1) Please don’t post answers as long and complicated as this one without explanation. (2) Egad! That contains FIND(CHAR(160),SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",CHAR(160),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ","")))) as a subexpression twice. That’s 136 characters! Use helper columns! Mar 23, 2019 at 19:00
0

For example your data is in cell A27.

=MID(LEFT(CONCATENATE(A27),FIND(".", A27)), FIND("!",SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","!",LEN(LEFT(CONCATENATE(A27),FIND(".",A27)))-LEN(SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","")))), (LEN(LEFT(CONCATENATE(A27),FIND(".", A27))))-(FIND("!",SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","!",LEN(LEFT(CONCATENATE(A27),FIND(".",A27)))-LEN(SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","")))))+1)

Hope it is useful.

1
  • (1) Please don’t post answers as long and complicated as this one without explanation. (2) Your answer looks superficially like Brian’s answer, which was posted four days earlier. Please explain how yours is better than his. Please do not respond in comments; edit your answer to make it clearer and more complete. Mar 23, 2019 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.