CODEWITHSUNDEEP
arraysexcelmatchvlookup
Torie
Torie

Reputation: 1

In Excel, how to return a string appearing after a string from a list (no delimiters)

I have a list of categories followed by string (no spaces or characters in between). I have the categories in a separate column and need to get the corresponding remaining string in the next column.

Here's an example: I have the following in Column D

Health and environmentNot Applicable
Labour standardsYes

In Column A I have the categories

Health and environment
Labour standards

and need Not Applicable and Yes to go in the corresponding row of column B.

I have about 200 categories and 2000 sets that I need to separate as described.

I have been playing around with vlookup, match, right, but cannot get it to work.

I am copying the data from a website and that's the only way it pastes into Excel. It is not always lower case followed by upper case; sometimes there is ")" in the end of the category. I could put the categories in a different sheet.

Upvotes: 0

Views: 37

Answers (2)

Binarus
Binarus

Reputation: 4405

In every case, you need to recognize when the category ends and the text begins. As far as I can see, you have two options:

1) Rely that the category always ends with a lowercase letter or a ), and that the text always begins with an uppercase letter, and vice versa. If you can rely on that, you can easily split the text from the category.

2) If you have a "database" with all possible category names, you can do a simple pattern match:

For each entry (which is a string), check if and where one of your category names is contained as a sub-string. If you find one, check the remaining string for the next category sub-string.

If you find one, the text between the first and the second category sub-string is normal text which belongs to the first category; store that category and text. If you don't find a next (second) category sub-string, the first category you had found is the only category in that entry, and the rest of the entry (the whole remaining string) is the text which belongs to that category.

Now cut off the leading category and its text from the entry. If the remaining entry is empty, you are done. If not, repeat the process described above with the remaining entry.

But be aware that this will fail if there are texts which accidentally contain category names.

Upvotes: 0

drec4s
drec4s

Reputation: 8077

If you paste in Column D that exact string that contains the corresponding category of Column A, then this formula in Column B gives the exact output you are looking for:

=MID(D2,LEN(A2)+1,LEN(D2)-LEN(A2))

EDIT:

If you need to, use a replace function to remove any unwanted characters like ")("

Upvotes: 1

Related Questions