Change list of states in a cell to their abbreviations

[Doug Mutzig]

Good morning all!

I just received an interesting request and I am not sure how to proceed. We have a table with one column that can hold 1 or more full state names separated by a semi-colon. What they would like is to have just the state abbreviations listed (see screenshot). Normally, I would use vlookup or index match in a new column to get the abbreviation but I don't know how to do this on multiple items within a cell. Is there a way to do this?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Doug Mutzig]

Thank you for the info. I have updated with my version (365 - Windows).

 

[Rick Rothstein]

Quesion: Will you ever have the state of Mississippi to abbreviate? I ask because its abbreviation is MS which is the same as you show for Midsouth (which along with Midwest is not a state).

 

[Fluff]

Thanks for updating your profile.
Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	California;Colorado;Midsouth;Midwest;Montana;	CA;CO;MS;MW;MT			California	CA
3	Midsouth;	MS			Colorado	CO
4					Maine	ME
5					Midsouth	MS
6					Midwest	MW
7					Montana	MT
Lists

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTJOIN(";",,XLOOKUP(FILTERXML("<k><m>"&SUBSTITUTE(LEFT(A2,LEN(A2)-1),";","</m><m>")&"</m></k>","//m"),E2:E7,F2:F7,"",0))

 

[Peter_SSs]

If the regions in the original list are in alphabetical order (like your samples) and Lookup table is also in alphabetical order (like your sample) then try the formula below.
If the values are not in the orders described above, the formula will still work but the abbreviations will come out in the order of the lookup table, not the order of the original data in column A - see row 7 for example. In any case this may not matter to you?

Doug Mutzig.xlsm
	A	B	C	D	E
1
2	California;Colorado;Iowa;	CA;CO;IA;		California	CA
3	Midsouth;Oregon;	MS;OR;		Colorado	CO
4	Maine;Midsouth;Ohio;	ME;MS;OH;		Indiana	IN
5	Midsouth;	MS;		Iowa	IA
6	California;Midsouth;	CA;MS;		Maine	ME
7	Maine;California;Ohio;Iowa;	CA;IA;ME;OH;		Midsouth	MS
8				Midwest	MW
9				Montana	MT
10				Ohio	OH
11				Oregon	OR
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(";",,FILTER(E$2:E$11,ISNUMBER(SEARCH(";"&D$2:D$11&";",";"&A2))))&";"

 

[Doug Mutzig]

Hi Flusff, and Peter_SSs. Than k you both for the great solutions. Fluffs solutions works great!

In answer to the question about Mississippi, currently we don't have plans for that to be listed, however, if it changes then I will need to update the list and probably break down the groups (midsouth and midwest) into their separate states.

Thank you again for the help on this!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Fluffs solutions works great!

Cheers. (Hopefully though you put some $ signs on the E/F ranges E$2:E$7,F$2:F$7 before copying down)