 SOLVED

Highlighted

# 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,

9 Replies
Highlighted
Best Response confirmed by Patrice Laliberté (Occasional Contributor)
Solution

# Re: Extract a number for a cell

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

Highlighted

# Re: Extract a number for a cell

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

Highlighted

# Re: Extract a number for a cell

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

Highlighted

# Re: Extract a number for a cell

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

Highlighted

# Re: Extract a number for a cell

Hi Twifoo,

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
Highlighted

# Re: Extract a number for a cell

Hi Karchavez,

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!
Patrice
Highlighted

# Re: Extract a number for a cell

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

Highlighted

# Re: Extract a number for a cell

This is perfect mtarler!

I will keeps all theses as exemples.

Have a nice day!

Patrice

Highlighted

# RE: Extract a number for a cell

you're very welcome. please mark as approved answer to close it out. thank you.