regex - Seeking to extract certain including ""n"p" where n is a digit or may not exist - St

admin管理员组

文章数量:1401947

Using Google Sheets: I'm trying to extract the price e.g. 250, 990 and 1760, the currency e.g. CZK, and problematically the text /p, /2p or /3p from some text field. Some example lines are shown below.

250CZK /p /night               

Extract in three cells 250, CZK, and /p

990CZK /night /2p room (/3p)   

Extract in three cells 990, CZK, and /2p

1760CHF /3p, 1375 /2p          

Extract in three cells 1760, CHF, and /3p

In the third example the 1375 /2p can be ignored. I only need the first instance. The sequence order of the entries can be random, e.g. I'd like the formula to be able to handle strings like:

/3p /night USD 550      

resulting in
Extract in three cells 550, USD, and /3p

Using Google Sheets: I'm trying to extract the price e.g. 250, 990 and 1760, the currency e.g. CZK, and problematically the text /p, /2p or /3p from some text field. Some example lines are shown below.

250CZK /p /night               

Extract in three cells 250, CZK, and /p

990CZK /night /2p room (/3p)   

Extract in three cells 990, CZK, and /2p

1760CHF /3p, 1375 /2p          

Extract in three cells 1760, CHF, and /3p

In the third example the 1375 /2p can be ignored. I only need the first instance. The sequence order of the entries can be random, e.g. I'd like the formula to be able to handle strings like:

/3p /night USD 550      

resulting in
Extract in three cells 550, USD, and /3p

• regex
• google-sheets
• text
• extract

Share Improve this question Follow edited Mar 25 at 0:41 TheMaster 51.2k77 gold badges7272 silver badges9999 bronze badges asked Mar 22 at 0:54 Mark DowtyMark Dowty 322 bronze badges 8

• CZK, CHF and USD appear to be currency abbreviations. is this correct? Is there a finite list of currencies that is used? – Tedinoz Commented Mar 23 at 6:52
• There is a finite list of currencies. There's only one or two additional ones actually (EUR and GBP). The /3p portion of this problem is really the once I'm struggling with at this point. I added the other questions in case there was a universal solution. – Mark Dowty Commented Mar 23 at 17:32
• By the way, I've tried various forms of REGEXEXTRACT to pull that portion to no avail. – Mark Dowty Commented Mar 23 at 17:34
• @MarkDowty Edit to add the code that you've tried. – TheMaster Commented Mar 23 at 18:34
• I am a REGEX novice but, according to my testing, a regex value of "\/\d?p" should return the first instance of the per person value. According to REGEX101 it should return "/p", "/2p", "/3p" and "/3p" but in my answer, it only returns "p", "2p", "3p" and "3p" and I had to manually add the "forward slash. I'm sure that there is a "correct" answer but it was/is beyond my limited abilities to figure it out. Allow "/" forward slash in regular expression was helpful. @Master perhaps you might have a look?? – Tedinoz Commented Mar 24 at 5:31

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

You want to analyse a number of strings that include a currency abbreviation, currency Value and per person value. Where there are more than one of any of these in any string, you want to return the first matching value.

Consider this script:

Logic

• the strings are taken from the spreadsheet and a loop is created to process each string
• An array, currencies, contains all the relevant currency codes.
• Each string value is analysed for:
  • Currency Code: a loop is created to process all currnecies; and indexOfis used to identify a match.
  • Currency value: The Javascript method String.prototype.match is used with regex value = \d{3,4} to find a number consisting of 3 or 4 digits.
  • Per person value: String.prototype.match is used again with regex = /\d?p/
    • the regex searches for the first instance of "/" and "p" and any digits between the two characters.
• Results are pushed onto a temporary array
• The temporary array is used to setValues to the spreadsheet.

---

function analyseString() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet3")
  var range = sheet.getRange("A3:A6")
  var stringValues = range.getValues()

  // get currency
  var currencies = ["CZK", "CHF", "USD", "EUR", "GBP"]

  // create a temportary array to hold results
  var results = []

  // loop through the stringValues
  for (var v=0;v<stringValues.length;v++){
    var string = stringValues[v][0]
    
    // loop through currencies and get a match
    for (var i=0;i<currencies[0].length;i++){
      var currencyCode = currencies[i] 
      var searchIdx = string.indexOf(currencyCode)
      if (searchIdx !=-1){
        break
      }
    }

  
    // get the Currency value
    var unitsValue = string.match(/\d{3,4}/);
  
    // get the perPerson value
    const persons = string.match(/\/\d?p/);
    Logger.log("v = "+v+", string value = "+string+", Unit value = "+unitValue+", Currency Code= "+currencyCode+", per persons = "+persons)

    // push results to a temporary array
    results.push([unitsValue,currencyCode,perPerson])

  }

  sheet.getRange(3,3,results.length,3).setValues(results)

}

---

SAMPLE - BEFORE

---

SAMPLE - console log

v = 0,
string value = 250CZK /p /night,
Unit value = 250,
Currency Code= CZK,
per persons = /p

v = 1,
string value = 990CZK /night /2p room (/3p),
Unit value = 990,
Currency Code= CZK,
per persons = /2p

v = 2,
string value = 1760CHF /3p, 1375 /2p,
Unit value = 1760,
Currency Code= CHF,
per persons = /3p

v = 3,
string value = /3p /night USD 550,
Unit value = 550,
Currency Code= USD,
per persons = /3p

---

SAMPLE - AFTER

本文标签：