Split row into columns in Excel

This example shows how to split a row into multiple columns in Excel.

The problem we’re dealing with in the figure above is that we need to tell Excel where to split the string. The line with the text “Smith, Mike” has a comma at position 6 (sixth character from the left), and the line with the text “Williams, Janet” has a comma at position 9.

1. To display only the name in another cell, use the formula below:

   =RIGHT(A2,LEN(A2)-FIND(",",A2)-1)

   =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(",";A2)-1)

   Explanation:

   • To find the position of a comma, use the function FIND (FIND) – position 6.
   • To get the length of a string, use the function LEN (DLSTR) – 11 characters.
   • The formula boils down to: =RIGHT(A2-11-6).
   • Expression =RIGHT(A2) extracts 4 characters from the right and outputs the desired result – “Mike”.
2. To display only the last name in another cell, use the formula below:

   =LEFT(A2,FIND(",",A2)-1)

   =ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1)

   Explanation:

   • To find the position of a comma, use the function FIND (FIND) – position 6.
   • The formula boils down to: =LEFT(A2-6).
   • Expression =LEFT(A2) extracts 5 characters from the left and gives the desired result – “Smith”.
3. Highlight a range B2: C2 and drag it down to paste the formula into the rest of the cells.