How to extract a substring between 2 separators

If you are using a PC (as opposed to a mac), you can use:

=FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(A2," ",","),",","</B><B>")&"</B></A>","//B[3]")

An alternative is using a classical formula as;

Please Login or Register  to view this content.

You may need to change semicolons to commas depending on your local settings.


Note: If you use Libre Office, its simple as;

Please Login or Register  to view this content.

and in Google Sheets;

Please Login or Register  to view this content.

You did not mention google sheets in your opening post. I have never used Google sheets. I will move this thread to the correct sub-forum.

Hi Laura,

The formula I offered for Google Sheets, uses the "Regular Expressions" method (also the formula for Libre Office)

"\s(\d+)" part of the formula is the "pattern" to look for in cell A1 where "\s" denotes a "space character" just before a group of digits which is modelled as "(\d+)".

The parentheses' are used to make a group where "\d" denotes a single digit but if you add a "+" sign, then you tell the formula to look for a group of digits followed by a space character.