Forum Discussion
Extract a number for a cell
Assuming your original string is in column A, and for any string with only "PO" you will want to show specific text 00.3 under column C (PO). Otherwise, extract the remainder of the string to the corresponding column B or C.
Under column B (B1 = Py): =IF($A2="PO","",IF(LEFT($A2,2)=$B$1,TRIM(MID($A2,3,100)),""))
Under column C (C1 = PO): =IF($A2="PO","00.3",IF(LEFT($A2,2)=$C$1,TRIM(MID($A2,3,100)),""))
Hello Patrice Laliberté ,
I strongly recommend to use an interactive formula, following solutions works even with spaces and is the same formula for how many columns you need to add. See attached the solution file.
If this works for you, please accept as a best response to help others with similar questions.
Solution 1:
If you need only extract the numbers as they are (text):
IFERROR(MID($B3,FIND(C$2,$B3,1)+LEN(C$2),LEN($B3)-FIND(C$2,$B3,1)+LEN(C$2)),"")
Solution 2:
Extract converted to values, in case you need to make operations with extracted numbers:
IFERROR(VALUE(MID($B3,FIND(C$2,$B3,1)+LEN(C$2),LEN($B3)-FIND(C$2,$B3,1)+LEN(C$2))),"")
Tanks for your response.
First time for me on the chat. And I'm having issues to make a reply. I keep having this message:
Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.
I have been able to send a response to Hynguyen, for more complex cases, if you have time to look at it, it would be appreciated.
Thanks again!
Patrice
Patrice Laliberté those are more complicated cases. I assume if there is no number or 00 after you want "tr" (trace?). Here is the formula I used in the attached starting in col O row 2 and creating a parallel set of cols for comparison:
=IFERROR(IF(SEARCH(O$1,$F2)>0,IFERROR(1/(1/(--TRIM(MID(SUBSTITUTE($F2,";",REPT(" ",10)),SEARCH(O$1,SUBSTITUTE($F2,";",REPT(" ",10)))+LEN(O$1),10)))),"tr"),""),"")
