Help

Re: Mid() + find() function needed

Solved
Jump to Solution
957 0
cancel
Showing results for 
Search instead for 
Did you mean: 
kxr1m
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

Bildschirmfoto 2021-07-03 um 11.15.41

1 Solution

Accepted Solutions
Chris-T
6 - Interface Innovator
6 - Interface Innovator

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!

See Solution in Thread

7 Replies 7
Chris-T
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

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

Chris-T
6 - Interface Innovator
6 - Interface Innovator

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.

image

image

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

kxr1m
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

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:

Screen Shot 2021-07-04 at 7.39.27 PM

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")