Abbreviation of Text

[Pestomania]

Hi,

I have a few options that populate on a label and I am trying to identify how to get the abbreviation (other than doing an if/then or a vlookup to another table).

Here are the options and the abbreviations:

Input Required	IR
Finished Good	FG
Non-Conforming Material	NM
Conforming Material	CM
Raw Material	RM
Non Inventory	NI
Government Furnished Equipment	GFE

By using formula:

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

I get the first two letters of the abbreviation. I used

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

to get the three letter identifier but it broke everything other than GFE because there was only one space.

Is there a formula that would provide the first letter after every space no matter the length of the statement?

 

[DanteAmor]

Hi @Pestomania.

Flash Fill automatically fills your data when it senses a pattern.

In this case, if the name "Input Required" is in cell A2, in cell B2 write the letters IR
In the cell below write the letters FG.
Now select cells B2 to B8, select from the menu Data, Flash Fill


Reference:

Using Flash Fill in Excel - Microsoft Support

Have Excel automatically fill your data when it detects a pattern. For example, the months of the year, the days in a month, or a sequence of numbers. You can also parse data, like splitting first and last names out of a column, or concatenate data, like joining first and last names from two...

support.microsoft.com

 

[Pestomania]

Hi Dante,

Hi @Pestomania.

Flash Fill automatically fills your data when it senses a pattern.

In this case, if the name "Input Required" is in cell A2, in cell B2 write the letters IR
In the cell below write the letters FG.
Now select cells B2 to B8, select from the menu Data, Flash Fill


Reference:

Using Flash Fill in Excel - Microsoft Support

Have Excel automatically fill your data when it detects a pattern. For example, the months of the year, the days in a month, or a sequence of numbers. You can also parse data, like splitting first and last names out of a column, or concatenate data, like joining first and last names from two...

support.microsoft.com

Thank you for that information. These abbreviations feed a field that is on a different excel sheet. This would work if I was using a VLookup, which I could do but was hoping to avoid if possible.

It is feeding a label like this:

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	Input Required	IR
3	Finished Good	FG
4	Non-Conforming Material	NM
5	Conforming Material	CM
6	Raw Material	RM
7	Non Inventory	NI
8	Government Furnished Equipment	GFE
Main

Cell Formulas
Range	Formula
B2:B8	B2	=CONCAT(LEFT(TEXTSPLIT(A2," ")))

 

[Pestomania]

How about

Fluff.xlsm
	A	B
1
2	Input Required	IR
3	Finished Good	FG
4	Non-Conforming Material	NM
5	Conforming Material	CM
6	Raw Material	RM
7	Non Inventory	NI
8	Government Furnished Equipment	GFE
Main

Cell Formulas
Range	Formula
B2:B8	B2	=CONCAT(LEFT(TEXTSPLIT(A2," ")))

As always Fluff, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.