How can we help?

New post
0

How to subtract the first word from a string?

Avatar
Jochem Cuppen
None
Follow

Hello, 

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?

Date Votes

2 comments

  • 0
    Avatar
    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") , " ") )

    returns

         Jochem
         John D

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

    Thanks,
             Janice

     

  • 0
    Avatar
    Jochem Cuppen

    Hello Janice,

    Thank you. This works!

    Best regards,

    Jochem

Please sign in to leave a comment.