Google Sheet Regex

 

  • You can use square brackets [] to match one of a set of characters. For example, if you wanted to match words beginning with a lower-case a or a lower-case b, you could use the pattern ^[a|b].*. The ^ character marks the beginning of a word, and the .* matches any characters that follow an a or a b.
  • You can define the end of a string by using the anchor $. For example, .*ly$ would match Finally and Gladly.
  • If you want to check for one of two regular expression components, you can use the pipe operator |. For example, Steve|Sarah would match Steve or Sarah.

Regex Formula with Filter
=FILTER(A1:E29,REGEXMATCH(B1:B29,H2))

Regex Formula with SUM AND Filter
=SUM(FILTER(D3:D30,REGEXMATCH(B3:B30,H4)))


Formula for if with regexmatch and regexreplace

In this exercise, you will use an IF() statement with REGEXMATCH() to find the branded campaigns, then replace the word Brand with Branded using REGEXREPLACE().

  • Recall that with REGEXMATCH(), you first supply the test cell, and then you supply the regex pattern.
  • With REGEXREPLACE() you supply the text (in this case the cell), your regex pattern, and the new string you want to swap in.

A pattern to match branded campaigns is already written for you in cell I2.


=IF(REGEXMATCH(B2,$I$2), REGEXREPLACE(B2, $I$2,"Branded"), B2)


Once again, the marketing director has put you in charge of complying with the social media marketing team's naming convention. This time, you are tasked with changing the ad group names to be more descriptive, so that the ad group names are a bit more informative.

To achieve this, you will need to add the word 'Buyers' to the end of the ad group name, while removing the s from the end of the word (e.g. Mugs will become Mug Buyers). Recall that using a $ can match the end of a string. - For example, ing$ will match the ing in flying.

Much as in the previous exercise, your formula will need to make use of IF(), REGEXMATCH(), and REGEXREPLACE().


  • Write a regular expression in cell I2 that will match the trailing s in words like Mugs and Sweaters. Use the 'flying' example above as a starting point for your pattern!
  • In cell F2, add an IF() statement:
    • Test if cell C2 contains your trailing s pattern $I$2.
    • When the argument is TRUE, replace the s (identified using your pattern) with " Buyers" (don't forget the space!)
    • When it is FALSE, return the string "No Match".
  • Apply the formula to the remainder of the cells, F3:F29.



=IF(REGEXMATCH(C2,$I$2),REGEXREPLACE(C2,$I$2," Buyers"),C2)



Extract brand campaign names using REGEXEXTRACT()

After some discussions with the social media marketing team, the digital marketing director decided to simply remove the terms Brand or Branded from each of the campaign names.

This is where you come in. You know how to replace using regex but, this time, you will be using REGEXEXTRACT() to pull all the characters not equal to Brand. For example, after the extraction, DataCamp Brand will become DataCamp.

To perform this extraction, you will need to write a new regular expression. As an example, the regex (.*).Expressions extracts any number of characters preceding the text and period, (i.e. Regular would be extracted from Regular Expressions).



  • A regular expression in cell I2 that will match any branded campaign (i.e. containing Brand) is already written for you in cell I2.
  • In cell I3, write another regular expression to extract the group of characters not equal to Brand. Refer to the example above for a starting point!
  • Modify the current IF() statement in cell F2 by replacing the second argument, "REGEXEXTRACT", with a REGEXEXTRACT() function.
    • The function should contain two arguments: the input text (in this case, B2), and the cell of your extract pattern.
  • Apply the formula to the remainder of the cells, F3:F29. Make sure that your pattern (I3) is an absolute reference!
IF(REGEXMATCH(B3,$I$2),REGEXEXTRACT(B3,$I$3),B3)




After much thought, and testing different naming conventions, the digital marketing director and the social media team have finally settled on a naming convention. They have decided to simply create campaign IDs.

These IDs will contain information about the source and campaign. For instance, the test IDs you create will be in the following format: the first letter from the Source, followed by an underscore, then the full Campaign Name. For example, the campaign ID for the source Yahoo with a campaign Spreadsheets would be: Y_Spreadsheets.

Recall that a begin anchor is ^. For example, ^f will match the f in flying. However, in this exercise you will need to be able to match the first letter from any word.


  • In cell I2, write a regular expression that will extract only the first character in a string.
  • In cell F2, write a formula to create the new test IDs:
    • Use REGEXEXTRACT() to pull the first letter from cell A2 using your pattern in I2 (as an absolute reference).
    • In the same cell, use t& characters to add a "_" and the campaign name (in cell B2).
  • Apply this formula to the remainder of the cells in the column, F3:F29.
Hint
  • A . will match any character. So you can use ^. as your pattern in I2.
  • Make sure the REGEXEXTRACT() function includes both A2 and I2 in the arguments.
  • The character & joins strings, and there should be two instances of & in this exercise.
  • Make sure that I2 is an absolute reference.

=REGEXEXTRACT(A2,$I$2)&"_"&B2


  • (^.{6}) would extract the first 6 characters of a string.
  • The first argument in REGEXEXTRACT() should be the cell with the string of interest, and the second should be the Regex.
  • The formula should resemble: REGEXEXTRACT(string, regular expression)&"_"&campaign ID


  • Write a REGEXMATCH() function to match the column A1:A29 with the value in the source dropdown, H2.
  • Working in cell H19 again, wrap the REGEXMATCH() function from the previous step with a FILTER() function, filtering for range A1:F29.
  • Finally, still in cell H19, wrap the FILTER() function with an IF() statement, where LEN(H2) > 1 as the condition, the FILTER() function as the value if True, and range A2:F29 if False
  • =IF(LEN(H2),FILTER(A1:F29,REGEXMATCH(A1:A29,H2)),TRUE).

Comments

Post a Comment

Popular posts from this blog

Binomial Test in Python

  Binomial Test Let’s imagine that we are analyzing the percentage of customers who make a purchase after visiting a website. 1000 customers visited the site this month, and 58 of them made a purchase. The marketing department reports that historical data suggests about 72 of every 1000 visitors make a purchase. Thus, they estimate that the probability of any particular customer making a purchase is 7.2%. We would like to know if this month’s number, 58 purchases, is significantly different from normal or a reasonable fluctuation due to random chance. In previous exercises, we collected samples of numerical information (eg. order price) and then used the mean and standard deviation of those samples to make comparisons. In contrast, we now have a sample where each unit (a visitor) falls into one of two discrete categories: “made a purchase” “did not make a purchase” Instead of comparing sample means, we want to compare the percent in the “made a purchase” category to some expectatio...
Read more

Slicing and Indexing in Python Pandas

 # Make a list of cities to subset on cities = ["Moscow", "Saint Petersburg"] # Subset temperatures using square brackets print(temperatures[temperatures["city"].isin(cities)]) # Subset temperatures_ind using .loc[] print(temperatures_ind.loc[cities]) temperatures_ind = temperatures.set_index(['country','city']) # List of tuples: Brazil, Rio De Janeiro & Pakistan, Lahore rows_to_keep = [('Brazil','Rio De Janeiro'),('Pakistan','Lahore')] # Subset for rows to keep print(temperatures_ind.loc[rows_to_keep]) # Sort temperatures_ind by index values print(temperatures_ind.sort_index()) # Sort temperatures_ind by index values at the city level print(temperatures_ind.sort_index(level="city")) # Sort temperatures_ind by country then descending city print(temperatures_ind.sort_index(level=["country", "city"], ascending = [True, False])) Slice rows with code like  df.loc[("a", ...
Read more

Python Syntax and Functions Part2 (Summary Statistics)

The  .agg()  method allows you to apply your own custom functions to a DataFrame, as well as apply functions to more than one column of a DataFrame at once, making your aggregations super efficient. In the custom function for this exercise, "IQR" is short for inter-quartile range, which is the 75th percentile minus the 25th percentile. It's an alternative to standard deviation that is helpful if your data contains   import numpy as np def iqr(column):     return column.quantile(0.75) - column.quantile(0.25) # Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment print(sales[["temperature_c", "fuel_price_usd_per_l", "unemployment"]].agg([iqr, np.median])) Calulating Cumulative Statistics Syntax = df.drop_duplicates sales_1_1 = sales_1_1.sort_values("date") # Get the cumulative sum of weekly_sales, add as cum_weekly_sales col sales_1_1["cum_weekly_sales"] = sales_1_1["weekly_sales...
Read more