0

Here is an extract from my spreadsheet: enter image description here

Please note: this is just a simplified example. My full spreadsheet has many columns and over 10,000 rows.

In my spreadsheet, I frequently have rows that have text in square brackets that begin with LIT: (As in the image).

Is it possible to automatically extract this text and put it in a separate column on the spreadsheet?

(E.g. So in the example above, [Lit: Next of hotel] would go in a seperate column, but would still remain in the same row). 


Note: As shown in the example, not every row has a [Lit:] example.

I am currently using Apple pages. But I am happy to try Google Docs or Open office if it can do it, or another package.

1

Your examples are conflicting about whether the string you want starts with [LIT: or [Lit:. I have assumed that the upper-case [LIT:.

In LibreOffice (and presumably other Excel equivalents, though I have no idea about Apple Pages or Google Docs), the FIND() function allows you locate a substring within a text field, but it returns an error if the substring is not found, so you need to use IFERROR() as well.

I shall consider first the simple example where any [LIT: field is always at the end of the string, with ] as the final character. If the data are in column A, starting at A1, then the following formula will do what you want:

=IFERROR(MID(A1,FIND("[LIT:",A1),LEN(A1)),"")

Here if FIND() returns a value, then the substring from that position to the end of the string is returned; otherwise, FIND() and therefore MID() will generate an error, and a blank string is returned.

In the more complex case, where the [LIT: field may occur in the middle of the string, the formula must be elaborated:

=IFERROR(MID(A1,FIND("[LIT:",A1),FIND("]",MID(A1,FIND("[LIT:",A1),LEN(A1)))),"")

In this case the substring [LIT: to end of string is found, but the number of characters generated from the original cell is limited by the position of ] within the substring; again, any error will generate a blank string.

Whichever formula you use, you copy the cell it's in and paste it down the rest of the column. If you need to handle either [LIT: or [Lit:, then replace FIND("[LIT:",A1) by SEARCH("\[L[Ii][Tt]:",A1): whereas FIND() looks for a literal, case-sensitive match, SEARCH() uses regular expression matching.

If you need to remove the [LIT: substring from the original column A, then put the extracted [LIT: field into column C, and put into B1:

=SUBSTITUTE(A1,C1,"",1)

Now copy this down the rest of column B and hide column A. Of course any columns and start rows could be used; for my examples I have used adjacent columns with no header rows.

Note that =SUBSTITUTE() doesn't generate errors, so there is no need to use IFERROR().

  • Thanks, that works brilliantly. Except, it leaves the LIT string in the original column. I was hoping to split it out, so the string is removed from the original column (and only occurs in the new column). Is there anyway to do this? Thanks again for your help, I greatly appreciate you taking the time to help me! – big_smile Mar 11 '18 at 11:13
  • 1
    That wasn't clear from your question. You can't remove text from the original column without scripting. What you can do is create a new column which contains the edited text, then hide the original column. – AFH Mar 11 '18 at 13:39
  • Thanks! Is there anyway to use a NOT statement (e.g. find everything that is Not [LIT:] and all the contents of the square brackets. That way, I could hide the original column and then create two new columns. One which contains all the LIT text (as you've shown with your formula) and then one which contains all the NOT LIT text. Thanks! – big_smile Mar 11 '18 at 18:14
  • 1
    It would be nice, but the only way I know to do it is to find the start of the [LIT: and output the characters to the left of this, concatenated with the characters to the right of a subsequent ], with any error generating the original string. – AFH Mar 11 '18 at 23:55
  • 1
    I thought the answer was an elaboration of searches and substrings (which I though you should be able to work out), but then I remembered a function that provides a more elegant answer, so I have updated my answer to use this. – AFH Mar 14 '18 at 18:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.