I have a column A containing text (Job post) in each cell. I want to extract job Salary (which is number like 2000, 20,000, 4.5k etc) into relevant cell D.

I have shared my google sheet which desired and current output column. here is google sheet

It takes a last 'word' that contains a number inside, removes all commas, parses it, mutiplies by 1000 if there is a 'k' and returns the result.

But parsing such texts is a tricky task. Basically there is no general simply solution.

CodePudding user response：

I will suggest that you do not need a script to accomplish this.

I've added a new sheet ("Erik Help") to your sample spreadsheet. In that sheet, I have placed a single formula in B2:

=ArrayFormula(IF(A2:A="",,VLOOKUP(REGEXEXTRACT(" "&REGEXEXTRACT(A2:A,CHAR(10)&". $")&" ","\s([\d\.,] ) k*\s")*IF(ISERROR(REGEXEXTRACT(" "&LOWER(A2:A),"\.\dk|\s\dk")),1,1000),SORT('Data Lookup'!A2:A),1,TRUE)))

This one formula will produce results for all rows (based on the limited current information in the sheet). It assumes that you want salary listings rounded to one of the numbers in the 'Data Lookup'!A2:A list.

I added into the formula a few extra parts that will assure you don't wind up extracting a number that you didn't mean to extract.

The formula assumes that the salary will always be listed in the final line of text, after the last line break (which I based on the format of the existing data in your sheet).

If this formula suits you, you can get rid of your script and simply use the formula. I always recommend using formulas where possible and reserving script use only for those times when it formulas cannot accomplish the same thing. Following this guideline reduces potential problems all around.