Forum Discussion
Solved
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 ...
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)),""))
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
