Split content in a cell based on key word

Akhila 1991
Akhila 1991 used Ask the Experts™
on
Hello,

I have content as mentioned below in a cell in EXCEL sheet. I wanted to split the content based on keywords "NAME", "EMPLOYER" and "DESIGNATION" into three different columns.

NAME: LOUIS GOERGE EMPLOYER: WALMART DESGINATION: MARKETING MANAGER.

Please let me know if this can be done using VB script or macro and help me with logic.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
I think you mean DESIGNATION and not DESGINATION ?

try look into this article:

How to extract text between two words in Excel?
https://www.extendoffice.com/documents/excel/1624-excel-extract-text-between-two-characters.html

hence, try like:

to get name:
=TRIM(MID(B2,SEARCH("NAME:",B2)+LEN("NAME:"),SEARCH("EMPLOYER:",B2)-SEARCH("NAME",B2)-LEN("NAME:")))

Open in new window


to get employer:
=TRIM(MID(B2,SEARCH("EMPLOYER:",B2)+LEN("EMPLOYER:"),SEARCH("DESIGNATION:",B2)-SEARCH("EMPLOYER:",B2)-LEN("EMPLOYER:")))

Open in new window


to get designation:
=TRIM(MID(B2,SEARCH("DESIGNATION:",B2)+LEN("DESIGNATION:"),LEN(B2)+1-SEARCH("DESIGNATION:",B2)-LEN("DESIGNATION:")))

Open in new window

29079446.xlsx
NorieAnalyst Assistant

Commented:
Are the key words always followed by a semi-colon?
Top Expert 2014
Commented:
Bill PrewTest your restores, not your backups...
Top Expert 2016
Commented:
And if you want a VBA macro that can process a whole column and split the values to the columns to the right (make sure they are blank), then take a look at this approach.

EE29079446.xlsm


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial