Forum Discussion

Deborah Thornburg's avatar
Deborah Thornburg
Copper Contributor
Sep 13, 2018

Auto Naming

I am working in a table where, for example, column B is "Job Number", Column C is "Order Date" and Column D is "Vendor".  I want to populate Column E automatically with the purchase order number whic...
excel
  • Deborah Thornburg's avatar
    Deborah Thornburg
    Copper Contributor
    Sep 14, 2018

    Oh my gosh thank you so much!  That is a huge help!  This program can do so much I'm not sure I will ever know it all!  Thanks again!

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Sep 14, 2018

      Ms. Thornburg

      credit also belongs to Mr. Michael M, jtakw and Peter_SSs, without them I can not complete the formula - they are responsible for the last part of it (vendor name abbrev).

      Am glad I can be of help...

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 15, 2018

        Lorenzo, great job.

         

        By the chance I'd like to share my collection of formulas to take initials. Results are like this

        and attached. As you may see some formulas assumes max number of words you have in the text (but definitely could be expanded).

        Formula here is

        =LEFT(B3,1)&
   LEFT(TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",100)),100,100)),1) &
   LEFT(TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",100)),200,100)),1) &
   LEFT(TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",100)),300,100)),1) &
   LEFT(TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",100)),400,100)),1)

        Chandoo suggested for 3 parts https://chandoo.org/wp/get-initials-from-name-excel-formula/

        =IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=0,
   LEFT(B3,1),
   IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,
      LEFT(B3,1)& MID(B3,FIND(" ",B3)+1,1),
      LEFT(B3,1)&
         MID(B3,FIND(" ",B3)+1,1)&
         MID(B3,FIND(" ",B3,FIND(" ",B3)+1)+1,1)
))

        Allen Wyatt make it more universal https://excelribbon.tips.net/T008663_Pulling_Initial_Letters_from_a_String.html

        =IF(ISERR(LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",
B3)+1)+1)+1)+1,1)),IF(ISERR(LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1)+1,1)),
IF(ISERR(LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)),
IF(ISERR(LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)),
IF(ISERR(LEFT(B3,1)),"",LEFT(B3,1)),LEFT(B3,1)
&MID(B3,SEARCH(" ",B3)+1,1)),LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)),
LEFT(B3,1)&MID(B3,SEARCH(" ",B3)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1)+1,1)),LEFT(B3,1)
&MID(B3,SEARCH(" ",B3)+1,1)&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)+1)+1,1)
&MID(B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3,SEARCH(" ",B3)+1)
+1)+1)+1,1))

        But if you are on Office 365 with CONCAT() function available the most universal will be this array formula (that's also not a my idea, but I don't know now who is the author)

        =LEFT(B3,1) &
CONCAT(
   IF(MID(B3,ROW(OFFSET($B$3,0,0,LEN(B3),1)),1)=" ",
      MID(B3,ROW(OFFSET($B$3,1,0,LEN(B3),1)),1),"")
)