Extracting variable digit number between last two quotation marks in the string-CodePudding

I have a long string in EXCELL that looks similar to this, but longer:

44625,Mercedes,44625.5984,"9487",8.99,469.515,"0.1102";"8272.1","0","12","3.63","129","32"

I need a formula to extract all the digits that are between last two quotation marks. In this example it would be 32.

The problem is that all numbers are variable, and because of that, they can have variable number of digits. So the last number can be "1234" or just "1".

Also, it can have variable number of decimal spaces, like this "123.22" or "11.12345"

I would like to extract the numbers only, without quotation marks, so that I can use this number for further operations.

Also, "text to columns" is not an option, since I need a formula.

I am using Excell for Microsoft 365 MSO Version 2201.

Best regards, Marko

CodePudding user response：

I think, you can use split() with quotation mark argument to function. And then, you can achieve your goal with last() method of string object.

CodePudding user response：

You need to use this formula as shown in image below,

Formula used in cell A3

=SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE($A1,",","</b><b>")&"</b></a>","//b[last()]"),"""","")

Special credit to

CodePudding user response：

Yet another formula, First reversing the string to get the last position of the quotation mark and then using Right() to get the last values and finally Substitute() to get just the digits. Reversing the string came from this SO post: How to reverse text in EXCEL without VBA Answer by Scott Craner

=SUBSTITUTE(RIGHT(A1,FIND("""",CONCAT(MID(A1,LEN(A1)-ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))) 1,1)),2)),"""","")

CodePudding user response：

If your string is formatted generally the way you show, with the last quoted value being at the end of the string, the following will work:

=TRIM(RIGHT(SUBSTITUTE(A1,"""",REPT(" ",99)),198))

That formula will return the value as text. To return it as a number, you can convert it with a double unary. eg:

=--TRIM(RIGHT(SUBSTITUTE(A1,"""",REPT(" ",99)),198))

If the last quoted value is not at the end of the string, or is not a numeric value, try:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"""","</s><s>") & "</s></t>","//s[number(.)=number(.)][last()]")

and if that doesn't work, supply a better example.