SOLVED

Extract a number for a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1425461%22%20slang%3D%22en-US%22%3EExtract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425461%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20extract%20the%20numbers%20from%20a%20column%20depending%20on%20the%20starting%20letters%3C%2FP%3E%3CP%3EORIG%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Py%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20PO%3C%2FP%3E%3CP%3EPy00.1%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2000.1%26nbsp%3B%3C%2FP%3E%3CP%3EPy01%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2001%3C%2FP%3E%3CP%3EPy0.01%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%200.01%3C%2FP%3E%3CP%3EPO0.2%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%200.2%3C%2FP%3E%3CP%3EPO%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2000.3%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1425461%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425477%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F218052%22%20target%3D%22_blank%22%3E%40Patrice%20Lalibert%C3%A9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20original%20string%20is%20in%20column%20A%2C%20and%20for%20any%20string%20with%20only%20%22PO%22%20you%20will%20want%20to%20show%20specific%20text%2000.3%20under%20column%20C%20(PO).%20Otherwise%2C%20extract%20the%20remainder%20of%20the%20string%20to%20the%20corresponding%20column%20B%20or%20C.%3C%2FP%3E%3CP%3EUnder%20column%20B%20(B1%20%3D%20Py)%3A%26nbsp%3B%3DIF(%24A2%3D%22PO%22%2C%22%22%2CIF(LEFT(%24A2%2C2)%3D%24B%241%2CTRIM(MID(%24A2%2C3%2C100))%2C%22%22))%3C%2FP%3E%3CP%3EUnder%20column%20C%20(C1%20%3D%20PO)%3A%26nbsp%3B%3DIF(%24A2%3D%22PO%22%2C%2200.3%22%2CIF(LEFT(%24A2%2C2)%3D%24C%241%2CTRIM(MID(%24A2%2C3%2C100))%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425534%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425534%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F218052%22%20target%3D%22_blank%22%3E%40Patrice%20Lalibert%C3%A9%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EI%20strongly%20recommend%20to%20use%20an%20interactive%20formula%2C%20following%20solutions%20works%20even%20with%20spaces%20and%20is%20the%20same%20formula%20for%20how%20many%20columns%20you%20need%20to%20add.%20%3CSTRONG%3ESee%20attached%20the%20solution%20file.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CU%3EIf%20this%20works%20for%20you%2C%20please%20accept%20as%20a%20best%20response%20to%20help%20others%20with%20similar%20questions.%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%201%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20you%20need%20only%20extract%20the%20numbers%20as%20they%20are%20(text)%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIFERROR(MID(%24B3%2CFIND(C%242%2C%24B3%2C1)%2BLEN(C%242)%2CLEN(%24B3)-FIND(C%242%2C%24B3%2C1)%2BLEN(C%242))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%202%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EExtract%20converted%20to%20values%2C%20in%20case%20you%20need%20to%20make%20operations%20with%20extracted%20numbers%3A%3C%2FP%3E%3CP%3EIFERROR(VALUE(MID(%24B3%2CFIND(C%242%2C%24B3%2C1)%2BLEN(C%242)%2CLEN(%24B3)-FIND(C%242%2C%24B3%2C1)%2BLEN(C%242)))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426007%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426007%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F218052%22%20target%3D%22_blank%22%3E%40Patrice%20Lalibert%C3%A9%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20D2%2C%20copied%20down%20and%20across%20the%20range%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(LEFT(%24A2%2CLEN(D%241))%3DD%241%2C%0A--SUBSTITUTE(%24A2%2CD%241%2C%22%22)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20results%20of%20the%20foregoing%20formula%20are%20shown%20in%20the%20snapshot%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ConditionalExtractNumber.PNG%22%20style%3D%22width%3A%20578px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195302iEC202E257A0808CF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22ConditionalExtractNumber.PNG%22%20alt%3D%22ConditionalExtractNumber.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426636%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETanks%20for%20your%20quick%20response%2C%20it%20works%20fine%20for%20cases%20that%20I%20asked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20actually%20the%20original%20file%20contains%20more%20complex%20cases.%20Here%20are%20some%20examples%20out%20of%2030%20000%20lines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETanks%20again!%3C%2FP%3E%3CP%3EPatrice%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426665%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426665%22%20slang%3D%22en-US%22%3EHi%20Twifoo%2C%3CBR%20%2F%3E%3CBR%20%2F%3ETanks%20for%20your%20response.%3CBR%20%2F%3EFirst%20time%20for%20me%20on%20the%20chat.%20ans%20I'm%20having%20issues%20responding.%20I%20keep%20having%20this%20message%3A%3CBR%20%2F%3E%3CBR%20%2F%3EYour%20post%20has%20been%20changed%20because%20invalid%20HTML%20was%20found%20in%20the%20message%20body.%20The%20invalid%20HTML%20has%20been%20removed.%20Please%20review%20the%20message%20and%20submit%20the%20message%20when%20you%20are%20satisfied.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20able%20to%20send%20a%20response%20to%20Hynguyen%2C%20for%20more%20complex%20cases%2C%20if%20you%20have%20time%20to%20look%20at%20it%2C%20it%20would%20be%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%3CBR%20%2F%3EPatrice%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426676%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426676%22%20slang%3D%22en-US%22%3EHi%20Karchavez%2C%3CBR%20%2F%3E%3CBR%20%2F%3ETanks%20for%20your%20response.%3CBR%20%2F%3EFirst%20time%20for%20me%20on%20the%20chat.%20And%20I'm%20having%20issues%20to%20make%20a%20reply.%20I%20keep%20having%20this%20message%3A%3CBR%20%2F%3E%3CBR%20%2F%3EYour%20post%20has%20been%20changed%20because%20invalid%20HTML%20was%20found%20in%20the%20message%20body.%20The%20invalid%20HTML%20has%20been%20removed.%20Please%20review%20the%20message%20and%20submit%20the%20message%20when%20you%20are%20satisfied.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20able%20to%20send%20a%20response%20to%20Hynguyen%2C%20for%20more%20complex%20cases%2C%20if%20you%20have%20time%20to%20look%20at%20it%2C%20it%20would%20be%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again!%3CBR%20%2F%3EPatrice%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426733%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F218052%22%20target%3D%22_blank%22%3E%40Patrice%20Lalibert%C3%A9%3C%2FA%3E%26nbsp%3B%20those%20are%20more%20complicated%20cases.%26nbsp%3B%20I%20assume%20if%20there%20is%20no%20number%20or%2000%20after%20you%20want%20%22tr%22%20(trace%3F).%26nbsp%3B%20Here%20is%20the%20formula%20I%20used%20in%20the%20attached%20starting%20in%20col%20O%20row%202%20and%20creating%20a%20parallel%20set%20of%20cols%20for%20comparison%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(IF(SEARCH(O%241%2C%24F2)%26gt%3B0%2CIFERROR(1%2F(1%2F(--TRIM(MID(SUBSTITUTE(%24F2%2C%22%3B%22%2CREPT(%22%20%22%2C10))%2CSEARCH(O%241%2CSUBSTITUTE(%24F2%2C%22%3B%22%2CREPT(%22%20%22%2C10)))%2BLEN(O%241)%2C10))))%2C%22tr%22)%2C%22%22)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426857%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20perfect%20mtarler!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20keeps%20all%20theses%20as%20exemples.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20nice%20day!%3C%2FP%3E%3CP%3EPatrice%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426890%22%20slang%3D%22en-US%22%3ERE%3A%20Extract%20a%20number%20for%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426890%22%20slang%3D%22en-US%22%3Eyou're%20very%20welcome.%20please%20mark%20as%20approved%20answer%20to%20close%20it%20out.%20thank%20you.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

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

Highlighted

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

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: 

ConditionalExtractNumber.PNG

Cheers,

Twifoo

Highlighted

@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 

Highlighted
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
Highlighted
Hi Karchavez,

Tanks for your response.
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

@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

@mtarler 

 

This is perfect mtarler!

 

I will keeps all theses as exemples.

 

Have a nice day!

Patrice

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