Mid() + find() function needed

[kxr1m]

hi dear community,

i need help finding the correct formula, but at least i know that the mid and find function have to be included.
i want to extract the text between both “-”, so for example “11 US” is visible in the “us size field”.

any help is greatly appreciated, if there are any questions regarding my concern, please let me know.

kind regards karim and thanks in advance! :slightly_smiling_face:

[Chris-T · Accepted solution]

Hi Karim. There are a couple of ways you can do this. One is with REGEX_EXTRACT()

REGEX_EXTRACT(txt,’- +(.*?)+ -’)

Another, is using MID() & FIND()

TRIM(MID(txt,FIND(’-’,txt)+1,FIND(’-’,txt,FIND(’-’,txt)+1)-FIND(’-’,txt)-1))

Hopefully, one of these work for you. Good luck!

[kxr1m]

hi chris, first of all thanks for your reply and your time. unfortunately none of the functions is working due to an error in the function. at first i copied your formula and then i tried to replace the “txt” with the fields name I’m referring to, but both options didn’t work out for me :frowning:

[ScottWorld]

Try replacing all the curly quotation marks with straight quotation marks.

[Chris-T]

Hi Karim. Sorry they didn’t work for you. Here’s a little shot that shows them both working. Did you copy/paste these from above or did you type them in? Make sure the quotes are not curly double quotes.

[Chris-T]

It’s interesting that they don’t look curly at all in the post editor, yet they do once published.

[kxr1m]

hi guys, first of all huge thanks to @Chris-T @ScottWorld !

it was like Scott said, the problem were the straight quotation marks and the “txt” didnt work out for me as well.
i ended up using the following function, which works now after keeping your tips in mind.

REGEX_EXTRACT({full size},"-+(.*?)±")

again thank you guys and stay safe! :slightly_smiling_face:

[Justin_Barrett]

Chris-T:

It’s interesting that they don’t look curly at all in the post editor, yet they do once published.

Always check the post preview on the right when editing to see how the text will appear when posted. To get quotes to remain unstyled for formulas or scripts, they must be formatted as preformatted text in the post editor. What you did in your posts was format them as quoted text, not as preformatted text. The preformatted text button in the editor toolbar is this one:

You can also manually do this using graves (the “inverted” apostrophe-like character that shares a key with the tilde on most keyboards: ` ). Single graves are fine for single lines, so that this:

`REGEX_EXTRACT(txt,’- +(.*?)+ -’)`

becomes this:

REGEX_EXTRACT(txt,'- +(.*?)+ -')

You can also add four spaces to the start of a line to format it as preformatted text. While this works easily enough for single lines, it becomes a pain for longer blocks of text. For multi-line formulas/scripts, wrap the lines in grave-triplet pairs, so this:

```
// Say my name
console.log(“Heisenberg”)
```

becomes this:

// Say my name
console.log("Heisenberg")