Concatenate first 3 letters of first word, and first 3 letters of second word

[JackChang]

Hi All,

My company is trying to create customer numbers using their name and they want to string the first 3 letters of the first word, and the first 3 letters of the second word. Each customer # should only be 6 characters.

There is an exception, they do not want to include special characters such as & * . _ , etc

For example:

Aloha Power Equipment = Alopow
Island Insurance Companies = Islins
Pacific Venture Associates LLC = pacven

Some vendors do not have 3 letters in their first name and the guidelines are unclear here. Anything will do for these in my opinion. I just strung together 3 letters using the first and second word, then take the next 3 letters from the third word.

A & R Painting Services = ARPSER
A to Z Roofing = AtoZro or AtoRoo

Is it possible to create a formula for this? If it's not possible for the exceptions I can always do it manually. Attached sample list.

Thank you

[June7]

Well, one way is to use Replace() to remove unwanted characters - for each character you expect could be in the string. Suggest you build a VBA custom function. Or better, abandon trying to do this at all. At some point, there will be a duplication, as in Carter Construction and Carson Consultants.

[georgiboy]

If using Excel 365 then maybe the below formula would give you a good start, you could also add a number on the end if need be:

=LET(d,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"&",""),"'",""),"-",""),".",""),
ts,TEXTSPLIT(d," "),
l,SCAN("",ts,LAMBDA(a,x,IF(LEN(x)<3,a&x,LEFT(x,3)))),
IFERROR(TEXTJOIN("",TRUE,CHOOSECOLS(FILTER(l,LEN(l)=3),1,2)),LEFT(SUBSTITUTE(d," ",""),6)))

[JackChang]

Thanks for your help georgiboy! I do have Excel 365 so the functions work. It's as good as it's going to get, just need to review the duplicates and manually change. Way better than doing all of them manually.

Thank you again for your time and consideration. Hope you have a great day.