Forum Discussion
Extract a number for a cell
Hi,
I have to extract the numbers from a column depending on the starting letters
ORIG Py PO
Py00.1 00.1
Py01 01
Py0.01 0.01
PO0.2 0.2
PO 00.3
Thanks,
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)),""))
9 Replies
- you're very welcome. please mark as approved answer to close it out. thank you.
Hi Patrice Laliberté ,
In the attached file, the formula in D2, copied down and across the range, is:
=IF(LEFT($A2,LEN(D$1))=D$1, --SUBSTITUTE($A2,D$1,""), "")The results of the foregoing formula are shown in the snapshot below:
Cheers,
Twifoo
- Hi Twifoo,
Tanks for your response.
First time for me on the chat. ans I'm having issues responding. 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
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))),"")
- Hi Karchavez,
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!
PatricePatrice 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"),""),"")
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)),""))
Tanks for your quick response, it works fine for cases that I asked.
But, actually the original file contains more complex cases. Here are some examples out of 30 000 lines.
Tanks again!
Patrice
