Ask Your Question

Extract part text from a cell

asked 2016-08-03 12:03:39 +0200

Cuxybam gravatar image

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
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-08-03 12:48:00 +0200

erAck gravatar image

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.

edit flag offensive delete link more


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..

Cuxybam gravatar imageCuxybam ( 2016-08-03 13:35:44 +0200 )edit

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.

erAck gravatar imageerAck ( 2016-08-03 21:16:44 +0200 )edit

answered 2016-08-03 12:27:24 +0200

Jean-Paul gravatar image


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)

edit flag offensive delete link more


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.

Cuxybam gravatar imageCuxybam ( 2016-08-03 12:50:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-03 12:03:39 +0200

Seen: 2,401 times

Last updated: Aug 03 '16