Table of contents

  1. Initials from first and last names
  2. Initials from first, middle and last names
  3. Create middle name initials
  4. How to enter an array formula

Initials from first and last names



Formula in cell B2:

=MID(TRIM(A2), 1, 1)&" "&MID(TRIM(A2), FIND(" ", TRIM(A2))+1, 1)

Initials from first, middle and last names

Array formula in cell B2:

=MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  1), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  2), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  3), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  4), 1)

How to enter an array formula

Copy cell B2 and paste down as far as needed.

Create middle name initials

Array formula in cell B2:

=MID(TRIM(A2), 1, FIND(" ", TRIM(A2)))&IFERROR(MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  IF(2<=COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2)))))), 2, "")), 1)&".", "")&IFERROR(" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  IF(3<=COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2)))))), 3, "")), 1)&".", "")&IF(COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2))))))=1, "", " ")&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ",  ROW($1:$99),  1),  " ")),  "",  ROW($1:$99)),  1+COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2))))))), 999)

How to enter an array formula

Copy cell B2 and paste down as far as needed.

How to enter an array formula

  1. Select cell B2
  2. Click in formula bar
  3. Paste array formula in formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter

Download excel *.xlsx file

Initials.xlsx