Extract String Part Between Single Quotes from Formula'S Divisor

Extract string part between single quotes from formula's divisor

Replace those characters with an empty string with REPLACE.

select ATTRFormula,
       Devider_col=
        REPLACE(
            substring(attrformula,charindex('/',ATTRFormula)+9,len(attrformula)-2),
            ''')',
            '')
from temp_mst_measure 
where attrtype='derived' 
and attrformula like '%/%'

Excel: How to extract text within a string of text until separator

One UDF Per Attribute

Create a UDF for each of your 5 categories and just use the formula as you usually would where the input is just your LONGFORM value. Here is a sample for Color. You would create a copy of this for each attribute and replace Color with the appropriate name.

Note: for price you will want to declare the function output as Double instead of String

Public Function Get_Color(Target As Range) As String

Dim i As Long, Temp
Temp = Split(Target, "|")

For i = LBound(Temp) To UBound(Temp)
    If InStr(Temp(i), "Color") Then
        Get_Color = Split(Temp(i), ":")(1)
        Exit Function
    End If
Next i

End Function

---

One UDF With Attribute Parameter

You can also use one UDF across the board and provide two inputs in the formula (LONGFORM & ATTRIBUTE) where attribute is either Color, Size, Category, COD, Price. Ideally you would just set the second parameter equal to the header of interest and drag down. It's worth noting that in current form the attribute parameter is case sensitive typo intolerant.

Public Function Get_Attribute(Target As Range, Element As String)

Get_Attribute = ""
Dim i As Long, Temp
Temp = Split(Target, "|")

For i = LBound(Temp) To UBound(Temp)
    If InStr(Temp(i), Element) Then
        Get_Attribute = Split(Temp(i), ":")(1)
        If IsNumeric(Get_Attribute) Then Get_Attribute = CDbl(Get_Attribute)
        Exit Function
    End If
Next i

End Function

Here is a sample of the second solution and how you would call the function. It would be similar for the One Attribute : One UDF approach except you would call different functions for each column but only give one input

Excel - extracting text between parentheses after finding certain text

With the big string in A1 and ABC in B1, try:

=LEFT(MID(A1,FIND(B1,A1)+LEN(B1)+2,9999),FIND(")",MID(A1,FIND(B1,A1)+LEN(B1)+2,9999))-1)

what is going on:

The core of the formula: MID(D1,FIND(E1,D1)+LEN(E1)+2,9999) discards the front end of the string and returns:12.3%) DEFGH (18.1%) IJKL (17.2%).

The enclosing part discards the closing parens and every that follows.

Google Sheets: How can I extract partial text from a string based on a column of different options?

Try using custom function:

To create custom function:

1.Create or open a spreadsheet in Google Sheets.

2.Select the menu item Tools > Script editor.

3.Delete any code in the script editor and copy and paste the code below into the script editor.

4.At the top, click Save save.

To use custom function:

1.Click the cell where you want to use the function.

2.Type an equals sign (=) followed by the function name and any input value — for example, =DOUBLE(A1) — and press Enter.

3.The cell will momentarily display Loading..., then return the result.

Code:

function matchTopic(p, str) {
  var params = p.flat(); //Convert 2d array into 1d
  var buildRegex = params.map(i => '(' + i + ')').join('|'); //convert array into series of capturing groups. Example (Dog)|(Puppies)
  var regex = new RegExp(buildRegex,"gi");
  var results = str.match(regex);
  if(results){
    // The for loops below will convert the first character of each word to Uppercase
    for(var i = 0 ; i < results.length ; i++){
        var words = results[i].split(" ");
        for (let j = 0; j < words.length; j++) {
          words[j] = words[j][0].toUpperCase() + words[j].substr(1);
        }
        results[i] = words.join(" ");
    }       
    return results.join(","); //return with comma separator
  }else{
    return ""; //return blank if result is null
  }
}

Example Usage:

Parameters:

First Topic:

Second Topic:

Third Topic:

Reference:

• Custom Functions

How to extract parsed data from once cell to another

Another possibility in LO Calc is to use the general purpose regular expression macro shown here: https://superuser.com/a/1072196/541756. Then the cell formula would be similar to JPV's answer:

=REFIND(A1,"([^-]+$)")

---

Related Topics