Split text in excel

Question

I have to divide the "Full Name" cells of my table into two.

Therefore, I used two functions: "Left" to take the person’s Name and "Right" to take the last name. In which, everything up to the first space is considered a name, and everything down to the last space is surname.

So, if the "Full Name" is registered as "Matthew Binatti", Name = Matthew, Surname = Binatti. Or, if the "Full Name" is registered as "Matthew of Binatti", Name = Matthew, Surname = of Binatti. So far, so good.

However, if the user registered his name as only Matthew, the field of name and surname return #VALUE, which is plausible, after all there is no space in the fill.

I would like to know ways of supplementing my formula to be able to eradicate these issues.

Name formula

=ESQUERDA($A2;LOCALIZAR(" "; $A2;1))

Formula for surname

=DIREITA($A2;NÚM.CARACT($A2)-LOCALIZAR(" ";$A2;1))

Answer 1

Use these formulas:

=SEERRO(ESQUERDA($A3;LOCALIZAR(" ";$A3;1));$A3)

=SEERRO(DIREITA($A3;NÚM.CARACT($A3)-LOCALIZAR(" ";$A3;1));"")

Where if there is an error in the left part of the name, the value of column A will be written. And if there is an error in the right part of the name, it will be written "" or Blank

Updating

One UDF (User-defined function) can be used to perform these operations. Just put the function inside a VBA module.

Code

Function EXTRACTELEMENT(Txt As String, n, Separator As String) As String
    On Error GoTo ErrHandler:
    EXTRACTELEMENT = Split(Application.Trim(Mid(Txt, 1)), Separator)(n - 1)
    Exit Function
ErrHandler:
    ' error handling code
    EXTRACTELEMENT = CVErr(xlErrNA)
    On Error GoTo 0
End Function

After inserting in the module you can use it in the spreadsheet as follows:

The data are entered with =EXTRACTELEMENT("String para separar ou célula com a String"; "Número do Elemento"; "separador(- .,:)")

The cell A1 has the desired input, you can use the function =EXTRACTELEMENT($A$1;1;"-") in the cell B1 and =EXTRACTELEMENT($A$1;2;"-") in the cell B2 and so on.

The result:

Description:

The description can be added by running the following code once:

Sub DescribeFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 3) As String

   FuncName = "EXTRACTELEMENT"
   FuncDesc = "Returns the nth element of a string that uses a separator character/Retorna o enésimo elemento da string que usa um caractér separador."
   Category = 7 'Text category
   ArgDesc(1) = "String that contains the elements/String que contém o elemento"
   ArgDesc(2) = "Element number to return/ Número do elemento a retornar"
   ArgDesc(3) = "Single-character element separator/ Elemento único separador (spc por padrão)"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub