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
It worked out, Daniel. But this gave me another question: If the user does not have Full Name registered, the function returns 0. I tried to fix the formula because it is necessary that the returned value is null, but I do not know why it is still returning zero. Follow my formula =SEERRO(LEFT($A2;FIND(" ";$A2;1));$A2);" ")
– Mateus Binatti
In this case you only use the
SE
, that if the value of column A is different from empty, the result is calculated, otherwise it is empty.=SE($A3<>"";SEERRO(ESQUERDA($A3;LOCALIZAR(" ";$A3;1));$A3);"")
– danieltakeshi