Text split in excel

Question

Text split in excel

Navigation

#1 by (4 votes)

4

I have to split the "Full Name" cells of my table in two.

So I used two functions: "Left" to get the Name of the person and "Right" to get the last name. In which, everything that is until the first space is considered a name, and everything that is until the last space is a last name.

So, if the "Full Name" is registered as "Matthew Binatti", Name = Matthew, Last Name = 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 first and last name field return #VALOR, which is plausible, after all there is no space in the padding.

I would like to know ways to complement my formula in order to eradicate these issues.

Formula for name

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

Formula for last name

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

excel

asked by anonymous 02.10.2017 / 19:59

1 answer

JavaScript function to add days, is adding up a month ahead What is the difference between Type-safe and Null-safe?

score 4 · Accepted Answer

Use these formulas:

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

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

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

Update

A 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 into the module you can use it in the worksheet as follows:

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

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

The result:

Description:

Thedescriptioncanbeaddedbyrunningthefollowingcodeonce:

SubDescribeFunction()DimFuncNameAsStringDimFuncDescAsStringDimCategoryAsStringDimArgDesc(1To3)AsStringFuncName="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