Forum Discussion

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 ...

Exemple.xlsx18 KB

excel

Formulas and Functions

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

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