Forum Discussion
How to solve this "VALUE" error in excel with just one formula
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 ?
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
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..."
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
