How to subtract the first word from a string?


I am currently working on a way to subtract the first word from a string in a table formula and not sure how to achieve this without the FIND function. I want to use first name only, so the formula should be made in such a way to only get the data before the first space (e.g. Jochem Cuppen should be Jochem).

Should I use SUBSTRING or LEFT, or any other formula?


  • 0
    Janice Janczyn

    Hi Jochem,

    You are on the right track with our text manipulation functions. Use the INDEXOF function to locate the space, as follows.

         SUBSTRING( "Jochem Cuppen", 0,  INDEXOF("Jochem Cuppen", " ") )

     Use the SUBSTRING function if you have more than 1 string to work with, for example, 

         SUBSTRING(  A:A, 0,  INDEXOF( A:A, " ") )
         SUBSTRING( ARRAY("Jochem Cuppen","John Doe"), 0,  INDEXOF( ARRAY("Jochem Cuppen","John Doe") , " ") )

    because SUBSTRING will match the INDEXOF results with the first parameter while LEFT will only look at the first value returned by INDEXOF.


         LEFT( ARRAY("Jochem Cuppen","John Doe"), INDEXOF( ARRAY("Jochem Cuppen","John Doe") , " ") )


         John D

    because the first value returned by INDEXOF( ARRAY("Jochem Cuppen","John Doe") , " ")  is 6.



  • 0
    Jochem Cuppen

    Hello Janice,

    Thank you. This works!

    Best regards,


Please sign in to leave a comment.