How To Abbreviate One + More Words in a Title to 6 Chars?

[wilkc]

Hi All,

I am trying to build a crosswalk of job titles so that the resulting values are confined to 6 characters in length. In essence I am looking to abbreviate job titles to 6 characters. Is there a text formula that can help accomplish this?

Below are some of the actual job titles (disregard the duplicates). I was thinking of using some type of rule to standardize this process. For example, in the case of one word titles, use the first 5 characters of the word then the last character, so that ABSTRACTOR = ABSTRR. For one word titles plus a level indicator use the first 5 letters of the first word followed by the first letter of the level indicator, so that ACCOUNANT I = ACCOUNI. Or perhaps keep the first letter of each word then remove the vowels then use the first 6 characters remaining, etc. so that ABSTRACTOR = ABSTRC.

Any help would be very much appreciated!!!!!!!

ABSTRACTOR</SPAN>

ACCOUNTANT I</SPAN>

ACCOUNTANT I</SPAN>

ACCOUNTANT I</SPAN>

ACCOUNTANT II</SPAN>

ACCOUNTANT II</SPAN>

ACCOUNTANT II</SPAN>

ACCOUNTANT III</SPAN>

ACCOUNTANT III</SPAN>

ACCOUNTANT III</SPAN>

ACCOUNTING CLERK II</SPAN>

ACCOUNTING CLERK II</SPAN>

ACCOUNTING CLERK II</SPAN>

ACCOUNTING CLERK III</SPAN>

ACCOUNTING CLERK III</SPAN>

ACCOUNTING CLERK III</SPAN>

ACCOUNTING CLERK TRAINEE</SPAN>

ACCOUNTING CLERK TRAINEE</SPAN>

ACCOUNTING CLERK TRAINEE</SPAN>

ACCOUNTING MANAGER</SPAN>

ACCOUNTING MANAGER</SPAN>

ACCOUNTING MANAGER</SPAN>

ADMINISTRATIVE SPECIALIST</SPAN>

ADMINISTRATIVE SPECIALIST</SPAN>

ADMINISTRATIVE SPECIALIST</SPAN>

ADOPTION/RESCUE COORDINATOR</SPAN>

ADULT DAY CARE AIDE</SPAN>

ADULT SERVICES AIDE</SPAN>

ADVANCED TRANSPORTATION WORKER</SPAN>

AGING SERVICES PROJECT COORDINATOR</SPAN>

AGING SERVICES SPECIALIST</SPAN>

AIRCRAFT & POWER PLANT MECHANIC</SPAN>

AIRPORT DISPATCHER II</SPAN>

AIRPORT DISPATCHER TRAINEE</SPAN>

AIRPORT DISPATCHER-OPERATIONS</SPAN>

AIRPORT DISPATCHER-PUBLIC SAFETY</SPAN>

AIRPORT POLICE CORPORAL</SPAN>

AIRPORT POLICE LIEUTENANT</SPAN>

AIRPORT POLICE OFFICER</SPAN>

AIRPORT POLICE OFFICER TRAINEE</SPAN>

AIRPORT POLICE SERGEANT</SPAN>

AIRPORT SYSTEMS AUTOMATION TECHNICIAN</SPAN>

AIRPORT SYSTEMS ENGINEERING TECHNICIAN I</SPAN>

AIRPORT SYSTEMS ENGINEERING TECHNICIAN II Thanks, Wilkc</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Akashwani]

Hi and welcome to MrExcel.

I don't have a solution, sorry, but.....

See if this VBA solution helps, you want to look at the post by Norie....

Extract Pull First Letter Of Each Word CONSISTENTLY

Another way, maybe, Post #7 by Peter_SSs......

http://www.mrexcel.com/forum/excel-questions/347338-picking-up-first-letter-each-word.html

I hope they help.

Good luck.

Ak

 

[wilkc]

We're getting closer ... I guess I need to fiddle with the code a bit. Keep the ideas coming though, I know others had to have a need to abbreviate titles.

 

[Akashwani]

Hi,

Take a look here to see if it helps....

Excel Magic Trick 559: Extract Nth Word In Text String - YouTube

I have compiled a "Table" but I am unsure how to create the final "Results" formula.
I don't have the formula knowledge or VBA skills to give you a final formula, sorry.

I hope the following gives you some idea on how complicated and difficult this may be to abbreviate the title down to 5/6/7 characters.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Job Title	Result		1st Word	2nd Word	3rd Word	4th Word	Last Word	# Words
2	ABSTRACTOR	ABS		ABS					1
3	ACCOUNTANT I	ACCI		ACC				I	2
4	ACCOUNTANT I	ACCI		ACC				I	2
5	ACCOUNTANT I	ACCI		ACC				I	2
6	ACCOUNTANT II	ACCII		ACC				II	2
7	ACCOUNTANT II	ACCII		ACC				II	2
8	ACCOUNTANT II	ACCII		ACC				II	2
9	ACCOUNTANT III	ACCIII		ACC				III	2
10	ACCOUNTANT III	ACCIII		ACC				III	2
11	ACCOUNTANT III	ACCIII		ACC				III	2
12	ACCOUNTING CLERK II	ACCCLEII		ACC	CLE			II	3
13	ACCOUNTING CLERK II	ACCCLEII		ACC	CLE			II	3
14	ACCOUNTING CLERK II	ACCCLEII		ACC	CLE			II	3
15	ACCOUNTING CLERK III	ACCCLEIII		ACC	CLE			III	3
16	ACCOUNTING CLERK III	ACCCLEIII		ACC	CLE			III	3
17	ACCOUNTING CLERK III	ACCCLEIII		ACC	CLE			III	3
18	ACCOUNTING CLERK TRAINEE	ACCCLETRA		ACC	CLE			TRA	3
19	ACCOUNTING CLERK TRAINEE	ACCCLETRA		ACC	CLE			TRA	3
20	ACCOUNTING CLERK TRAINEE	ACCCLETRA		ACC	CLE			TRA	3
21	ACCOUNTING MANAGER	ACCMAN		ACC				MAN	2
22	ACCOUNTING MANAGER	ACCMAN		ACC				MAN	2
23	ACCOUNTING MANAGER	ACCMAN		ACC				MAN	2
24	ADMINISTRATIVE SPECIALIST	ADMSPE		ADM				SPE	2
25	ADMINISTRATIVE SPECIALIST	ADMSPE		ADM				SPE	2
26	ADMINISTRATIVE SPECIALIST	ADMSPE		ADM				SPE	2
27	ADOPTION/RESCUE COORDINATOR	ADOCOO		ADO				COO	2
28	ADULT DAY CARE AIDE	ADUDAYCARAID		ADU	DAY	CAR		AID	4
29	ADULT SERVICES AIDE	ADUSERAID		ADU	SER			AID	3
30	ADVANCED TRANSPORTATION WORKER	ADVTRAWOR		ADV	TRA			WOR	3
31	AGING SERVICES PROJECT COORDINATOR	AGISERPROCOO		AGI	SER	PRO		COO	4
32	AGING SERVICES SPECIALIST	AGISERSPE		AGI	SER			SPE	3
33	AIRCRAFT & POWER PLANT MECHANIC	AIR& POWPLAMEC		AIR	&	POW	PLA	MEC	5
34	AIRPORT DISPATCHER II	AIRDISII		AIR	DIS			II	3
35	AIRPORT DISPATCHER TRAINEE	AIRDISTRA		AIR	DIS			TRA	3
36	AIRPORT DISPATCHER-OPERATIONS	AIRDIS		AIR				DIS	2
37	AIRPORT DISPATCHER-PUBLIC SAFETY	AIRDISSAF		AIR	DIS			SAF	3
38	AIRPORT POLICE CORPORAL	AIRPOLCOR		AIR	POL			COR	3
39	AIRPORT POLICE LIEUTENANT	AIRPOLLIE		AIR	POL			LIE	3
40	AIRPORT POLICE OFFICER	AIRPOLOFF		AIR	POL			OFF	3
41	AIRPORT POLICE OFFICER TRAINEE	AIRPOLOFFTRA		AIR	POL	OFF		TRA	4
42	AIRPORT POLICE SERGEANT	AIRPOLSER		AIR	POL			SER	3
43	AIRPORT SYSTEMS AUTOMATION TECHNICIAN	AIRSYSAUTTEC		AIR	SYS	AUT		TEC	4
44	AIRPORT SYSTEMS ENGINEERING TECHNICIAN I	AIRSYSENGTECI		AIR	SYS	ENG	TEC	I	5
45	AIRPORT SYSTEMS ENGINEERING TECHNICIAN II	AIRSYSENGTECII		AIR	SYS	ENG	TEC	II	5
46
Sheet7

All the formulas need to be copied down.

I hope this helps and I'm sorry that I couldn't provide you with a final solution, hopefully someone else will jump up and solve this for you.

Good luck.

Ak