Forum Discussion

Patrice Laliberté's avatar
Patrice Laliberté
Copper Contributor
May 29, 2020
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                 ...
excel
Formulas and Functions
  • hynguyen's avatar
    hynguyen
    May 29, 2020

    Patrice Laliberté 

    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)),""))

hynguyen's avatar
hynguyen
Iron Contributor
May 29, 2020

Patrice Laliberté 

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)),""))

  • Patrice Laliberté's avatar
    Patrice Laliberté
    Copper Contributor
    May 29, 2020

    hynguyen 

     

    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 

Resources