How to solve this "VALUE" error in excel with just one formula-CodePudding

I want to extract this part => "1.3215809385571036" from following text and I use this formula {=MID(LEFT(A2;FIND(",";A2)-1);FIND("_";A2) 7;18)} for this.

4_1100_ 1.3215809385571036, X-Ray checking, Kino checking, GPS avto (kadr)

But sometimes my text is looking like this and formula return "VALUE" error because "Find" function can not find "," and return error.

1_1100_ 1.2152157818772644 Green gas. fekfmf eujffmmef. GPS chechking.

How to solve this problem? How to write this formula for ....FIND(",";A2) and also FIND(" G";A2) with just one formula ?

CodePudding user response：

Try:

Formula in B1:

=MID(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,","," ")," ","</s><s>'")&"</s></t>","//s[2]"),2,LEN(A1))

Or, stuff like:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",LEN(A1)),2),FIND(" ",A1) 1,LEN(A1)))

However,for those lucky ones with access to TEXTSPLIT(), you can use:

=INDEX(TEXTSPLIT(A1,{" ",","}),2)

CodePudding user response：

You may try this as well,

• Formula used in cell B1

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",100)),100,100))