May 28 2020 06:32 PM
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,
May 28 2020 06:45 PM - edited May 28 2020 06:46 PM
SolutionAssuming 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)),""))
May 28 2020 07:58 PM
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))),"")
May 29 2020 01:21 AM
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
May 29 2020 06:54 AM
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
May 29 2020 07:06 AM
May 29 2020 07:08 AM
May 29 2020 07:24 AM
@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"),""),"")
May 29 2020 08:17 AM
May 29 2020 08:27 AM
May 28 2020 06:45 PM - edited May 28 2020 06:46 PM
SolutionAssuming 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)),""))