Extract part text from a cell

[Cuxybam]

I want to extract part of the cell content matching a specific pattern and put in another cell.

Example: “Find What is the size?=Small”, remove “What is the size?=” and put “Small” in the new cell. The problem is “Small” doesn’t follow a particular length, pattern or list. It could be “Medium”, “XL”, “Big” etc.

Also the pattern can be anywhere in the string, also in the end. So I cannot necessarily assume there will be a comma at the end.

Here is three sample rows:

What is the size?=Small, Style=Super chick, How often?=Every 14. day, Which day=Friday, How long?=2 months, Note to order=Hello, thanks for the order

Style=Super chick, Which day=Friday, How long?=2 months, Note to order=Hello, thanks for the order, How often?=Every 14. day, What is the size?=Small

Style=Super chick, Which day=Friday, Note to order=Hello, thanks for the order, What is the size?=Small, How long?=2 months, How often?=Every 14. day

What is the size?=Small, Note to order=Hello, thanks for the order

[Jean-Paul1]

Hello,

If your text “Find What is the size?=Small” is in cell A1,

try the following function :

=RIGHT(A1;LEN(A1)-LEN(“Find What is the size?=”)-FIND(“Find What is the size?=”;A1)+1)

[Cuxybam]

Thanks for the fast relpy. It seems that the only thing that happen is that “What is the size?=” get removed. Also “Find” Apparently should be there either.

[erAck]

First, I assume you want to split that on arbitrary data (question&answer), not just for “What is the size?=”.

Second, I assume that all data is paired data, i.e. if there is a question then there’s also an answer, even if empty, but then it would be =, or = , if somewhere within the cell.

Select your data column and invoke menu Data -> Text to Columns..., in Separator Options choose Comma and Other and in the Other field enter the = equal sign. Verify in preview that the records are split as desired and hit OK.

You then have a table where alternating every first column is a question and every second column is the corresponding answer.

[Cuxybam]

A pretty good solution. But the question/answer is not always in the same position in the text. Therefore the columns will not match up…

[erAck]

Then you best go with helper columns, like, assuming data starts in row 2:

in O1: What is the size?=

in M2: =FIND(O$1,A2,1)+LEN(O$1)

in N2: =IFERROR(FIND(",",A2,M2),LEN(A2)+1)

in O2: =MID(A2,M2,N2-M2)

With this you can select M2:O2 and copy&paste it down to other rows.