SOLVED

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

New Contributor

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 formuLLa ?

5 Replies

@turan_aghayev You seem to want to extract the 18 characters, starting at the ninth. Why not just use:

=MID(A2;9;18)

 

Alternatively, if the first underscore "_" may be in different places sometimes, use:

=MID(A2;FIND("_";A2)+7;18)

Thanks for answer, because sometimes amount of characters of substring is different in my data. Sometimes 16, 17 and sometimes 18 characters. That's why I want to use formula which extract text before and after special characters like "_", "," and sometimes " G..."

best response confirmed by turan_aghayev (New Contributor)
Solution

@turan_aghayev Then use this part to either find a comma or " G".

IFERROR(FIND(",";A2);FIND(" G";A2))-1

 

rather than just FIND(",";A2)-1

That's great. I do not have information about "Iferror" function. It is working in this formula {=MID(LEFT(A2;IFERROR(FIND(",";A2);FIND("Y";A2))-1);FIND("_";A2)+7;18)}
Thanks.