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),"")
)
    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Sep 15, 2018

      Mr. Baklan

      thank you for the kind words.

      also for the file - new addition to my archive of formula & codes.

      my excel is 2013 so the concat isn't working - I have seen that code from Mr. Peter_SSs.

      by far, the shortest formula of them all..

      but it will be a nice reference in the future.

      thank you very much for the info..

      • Deborah Thornburg's avatar
        Deborah Thornburg
        Copper Contributor
        Sep 18, 2018

        Oh my, I want to follow along with all those codes but I am so confused.  So, what if I wanted to reference a table such as:

          

        Instead of having the string pull the first letter of each word, how can I get it to reference the table and pull the existing vendor code?  I feel as though the IF function is way below the level of code you guys use but I tried it and it said there were too many arguments to go through the entire table.