6 Replies Latest reply on Aug 21, 2018 9:33 PM by Naveen B

    Splitting First and Last name

    Bhuvnesh Elango

      [Last Name]

      CASE LEN([Name]) - LEN(REPLACE([Name], ' ',''))+1

          WHEN 2 THEN SPLIT([Name],' ',2)

          WHEN 3 THEN SPLIT([Name],' ',3)

      END

       

      Can someone explain me how this code works, I know it will split the last name of an user but can you guys tell me what each line of this code does. Kindly help me out with this.

        • 1. Re: Splitting First and Last name
          Ritesh Bisht

          Hi Bhuvnesh,

           

          Is there any reason that you are not using below which is a simpler version

           

          TRIM( SPLIT( [Customer Name], " ", -1 ) )

           

           

          Screen Shot 2018-08-19 at 6.13.09 PM.png

           

          So, we are using -1 here because we want to extract the character from the end .

           

          If you want 'd' in above example then you will use -1 .

           

          In your case space is the delimiter hence using ' ' instead.

           

          Thanks,

          Ritesh

          Please mark the answer as CORRECT & HELPFUL if it really helped you so that it can help others as well.

           

          Thanks,

          Ritesh

          1 of 1 people found this helpful
          • 2. Re: Splitting First and Last name
            Bhuvnesh Elango

            Hi Ritesh,

             

            Firstly Thanks for your help, I was given a problem where I should split the last name from the user name and I looked online for an example syntax and got this one. I tried and it worked but I didn't understand what this piece of code does. But yours is way too simpler.

            • 3. Re: Splitting First and Last name
              Naveen B

              Hi Bhuvnesh,

               

              Let's break down the below formula step by step

               

              CASE LEN([Name]) - LEN(REPLACE([Name], ' ',''))+1

                  WHEN 2 THEN SPLIT([Name],' ',2)

                  WHEN 3 THEN SPLIT([Name],' ',3)

              END

               

              1) LEN([Name]) - It will give the length of the string

              Eg: Naveen Bandla - LEN(Naveen Bandla) will give 13 value

               

              2) LEN(REPLACE([Name], ' ',''))

                   i) REPLACE([Name], ' ','')

                      Eg: Naveen Bandla - REPLACE(Naveen Bandla, ' ','') - Will Give NaveenBandla by removing the space

                  ii) LEN(NaveenBandla) - Will give 12 Value

               

              3) SPLIT([Name],' ',2)

              Eg: SPLIT(Naveen Bandla,' ',2) - It will take space as the delimiter and gives the Second value after splitting so it would be Bandla

               

              4) SPLIT([Name],' ',3)

              Eg: SPLIT(Naveen Reddy Bandla,' ',3)-It will take space as the delimiter and gives the Third value after splitting so it would be Bandla

               

              Based on your dataset releate this formula breakdown so that it will be helpful to you

               

              If this helps kindly mark this answer as correct or helpful so that it will help others

               

              BR,

              NB

              1 of 1 people found this helpful
              • 4. Re: Splitting First and Last name
                Ritesh Bisht

                Great Buvnesh , I am glad that it helped you Yes, you can learn the syntax/logic  but why to complicate it 

                 

                Please hit the Correct Button as well so that others can also benefit with this simpler version .

                 

                Cheers ,

                Ritesh

                • 5. Re: Splitting First and Last name
                  Bhuvnesh Elango

                  Hi Naveen

                   

                  Thanks a lot for your explanation but also sorry for the late response I was clouded with exams.

                  • 6. Re: Splitting First and Last name
                    Naveen B

                    Hi Bhuvnesh,

                     

                    If it helps kindly mark the relevant answer as correct by clicking on the below link

                     

                    Splitting First and Last name

                     

                    From inbox its not possible mark the correct answer.

                     

                    Once it marked it will be moved to marks section and will be helpful for the people who are looking for the same answer

                     

                    BR,

                    NB