• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 105
  • Last Modified:

Split content in a cell based on key word

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.
0
Akhila 1991
Asked:
Akhila 1991
3 Solutions
 
Ryan ChongCommented:
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
1
 
NorieVBA ExpertCommented:
Are the key words always followed by a semi-colon?
0
 
Bill PrewCommented:
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
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now