• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Expert

      I need a macro to do the following….

      Macro for trimming the extra spaces and concatenating  Lastname and initials of given names

      If last name is in column A and given names in column B

      if I put this in column C

      =TRIM(A1) &” , “&TRIM(LEFT(B1,1)&IF(ISERROR(FIND(” “,B1,1)),””,MID(B1,FIND(” “,B1,1)+1,1))&IF(ISERROR(FIND(” “,B1,FIND(” “,B1,1)+1)),””,MID(B1,FIND(” “,B1,FIND(” “,B1,1)+1)+1,1)))

      I am getting  BRAGANZA  , GBJ

      If I delete the columns A and B, then the column C becomes blank!

      But, I want to make it look like this.

      BRAGANZA                         GERARD BERNARD JOSEPH                          Braganza, GBJ

      EDWARDS                           PHILLIP JON                                                        Edwards, PJ

      SMITH                                   DANNY STUART                                                Smith, DS

      MCKEOWN                         TERRY DESMOND                                             McKeown, TD

      KEAR                                     DAVID ANDREW                                               Kear, DA

      WARD                                   STEPHEN CHARLES                                           Ward, SC

      Then I want to delete Column A and column B.

      Asked by raghuprabhu on May 12, 2017 in Excel.
      3 Answers
      Expert

      HI,

      Before deleting Column A and Column B copy 3rd column  data and paste as special values. Now delete Column A and Column B.

      Regards!

      Answered by Valli on May 12, 2017..
      Expert

      Thank sorted out…

      Answered by raghuprabhu on May 13, 2017..
      Expert

       

      Sub ConcatenateSurnameAndInitials()
      'Concatenates initials from given names (column 2) to proper surname in column 3
      Dim a, j As Long
      With Cells(1).CurrentRegion.Resize(, 3)
      a = .Value
      For j = 1 To UBound(a, 1)
      a(j, 3) = Trim$(StrConv(a(j, 1), 3)) & IIf(a(j, 2) <> "", ", ", "") & GetInitial(a(j, 2))
      Next
      .Value = a
      End With
      End Sub
      ials from given names (column 2) to proper surname in column 3
      Dim a, j As Long
      With Cells(1).CurrentRegion.Resize(, 3)
      a = .Value
      For j = 1 To UBound(a, 1)
      a(j, 3) = Trim$(StrConv(a(j, 1), 3)) & IIf(a(j, 2) <> "", ", ", "") & GetInitial(a(j, 2))
      Next
      .Value = a
      End With
      End Sub
      

       

      Function GetInitial(ByVal txt As String) As String
      Dim m
      With CreateObject("VBScript.RegExp")
      .Global = True: .IgnoreCase = True
      .Pattern = "\b[A-Z]"
      For Each m In .Execute(txt)
      GetInitial = GetInitial & m
      Next
      End With
      End Function
      
      Answered by raghuprabhu on May 13, 2017..