Forum Discussion

Jamie Batie's avatar
Jamie Batie
Copper Contributor
Aug 25, 2017

Formatting Rule or Conditional formatting?

Good Morning! 

I was trying to see if there was a Formatting Rule or a Conditional Formatting function that I could use, to shorten company names, to 4 letters, automatically. All of the information is pasted into a "MASTER FORM" where the header stays the same, every day. But I spend time, shortening company names to around 4-6 letters (around 200 jobs).

 

12345PURPLE MACHINING
12346AVERY REMOVEABLE
12347HABIT GRILL
12348GOVERNMENT
12349BOEING
12350NICK ENGINEERING
12351ROMER 
12359TESSA LITE
excel
Formulas and Functions

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 25, 2017
    You could remove all vowels and spaces and then take the first 6 characters:
    =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A",""),"E",""),"O",""),"I",""),"U","")," ",""),6)

Resources