SOLVED
Home

Help with Excel´s formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-815119%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%C2%B4s%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815119%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20separate%20the%20numbers%20in%20the%20column%20%22ARTICLE%22%20and%20copy%20them%20into%20the%20column%20%22SIZE%22.%20For%20the%20Excels%20cells%20without%20any%20number%20in%20the%20content%2C%20I%20would%20like%20that%20in%20the%20%22SIZE%22%20column%20appears%20an%20N%2FA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditionally%2C%20this%20information%20comes%20from%20a%20different%20book%2C%20so%20it%20has%20copied%20the%20original%20format%20and%20not%20the%20information%20from%20the%20cell.%20So%20instead%20of%20having%20a%20column%20saying%20i.e.%3A%20%22Scarf%22%2C%20the%20Excel%20cell%20says%20something%20like%3A%20%22Uniforms%20control'!U4%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3EOriginal%20table%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EARTICLE%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESIZE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EScarf%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%20docker%20trouser%2042%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EMale%20Navy%26nbsp%3B%20pleat%20Trousers%2040%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ETie%20Clip%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%20docker%20trouser%2034%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%202%20Button%20Jacket%2038%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpected%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3EARTICLE%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3ESIZE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EScarf%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EN%2FA%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%20docker%20trouser%2042%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E42%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EMale%20Navy%26nbsp%3B%20pleat%20Trousers%2040%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E40%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3ETie%20Clip%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EN%2FA%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%20docker%20trouser%2034%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E34%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EFemale%20Navy%202%20Button%20Jacket%2038%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E38%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-815119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815271%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%C2%B4s%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139854%22%20target%3D%22_blank%22%3E%40Jos%C3%A9%20Lorenzo%20Str%C3%A9del%20Garc%C3%ADa%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(--RIGHT(TRIM(A2)%2C1))%2C%3CBR%20%2F%3EMID(SUBSTITUTE(A2%2C%22%20%22%2C%22%2C%22%2CLEN(A2)-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22)))%2C%3CBR%20%2F%3ESEARCH(%22%2C%22%2CSUBSTITUTE(A2%2C%22%20%22%2C%22%2C%22%2CLEN(A2)-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22))))%2B1%2C%3CBR%20%2F%3ELEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%2C%22%2CLEN(A2)-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22))))-SEARCH(%22%2C%22%2CSUBSTITUTE(A2%2C%22%20%22%2C%22%2C%22%2CLEN(A2)-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22))))%2B1)%2C%3CBR%20%2F%3E%22N%2FA%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20761px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127826iDC90EBD1F4A43C4B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-08-21_18-04-03.png%22%20title%3D%222019-08-21_18-04-03.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815293%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%C2%B4s%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815293%22%20slang%3D%22en-US%22%3E%3CP%3EGreat!!!%20it%20works%20perfectly.%20Many%20thanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815313%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%C2%B4s%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139854%22%20target%3D%22_blank%22%3E%40Jos%C3%A9%20Lorenzo%20Str%C3%A9del%20Garc%C3%ADa%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20you%20use%20this%20shorter%20formula%20as%20well%3A%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(--RIGHT(TRIM(A2)%2C1))%2C%3CBR%20%2F%3ETRIM(RIGHT(A2%2CSUM(LEN(A2)-LEN(SUBSTITUTE(A2%2C%7B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%2C%227%22%2C%228%22%2C%229%22%7D%2C%22%22)))))%2C%3CBR%20%2F%3E%22N%2FA%22)%3C%2FPRE%3E%3CP%3EIt's%20an%20updated%20version%20of%20the%20formula%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2017%2F11%2F22%2Fexcel-extract-number-from-string%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
José Lorenzo Strédel García
Occasional Contributor

Hi,

 

I would like to separate the numbers in the column "ARTICLE" and copy them into the column "SIZE". For the Excels cells without any number in the content, I would like that in the "SIZE" column appears an N/A.

 

Additionally, this information comes from a different book, so it has copied the original format and not the information from the cell. So instead of having a column saying i.e.: "Scarf", the Excel cell says something like: "Uniforms control'!U4"

 

Original table

ARTICLE

SIZE

Scarf

 

Female Navy docker trouser 42

 

Male Navy  pleat Trousers 40

 

Tie Clip

 

Female Navy docker trouser 34

 

Female Navy 2 Button Jacket 38

 

 

Expected result:

 

ARTICLE

SIZE

Scarf

N/A

Female Navy docker trouser 42

42

Male Navy  pleat Trousers 40

40

Tie Clip

N/A

Female Navy docker trouser 34

34

Female Navy 2 Button Jacket 38

38

 

3 Replies
Solution

@José Lorenzo Strédel García

 

Hi,

 

Please try this formula:

=IF(ISNUMBER(--RIGHT(TRIM(A2),1)),
MID(SUBSTITUTE(A2," ",",",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),
SEARCH(",",SUBSTITUTE(A2," ",",",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,
LEN(SUBSTITUTE(A2," ",",",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-SEARCH(",",SUBSTITUTE(A2," ",",",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1),
"N/A")

 

2019-08-21_18-04-03.png

 

Hope that helps

Great!!! it works perfectly. Many thanks @Haytham Amairah 

@José Lorenzo Strédel García

 

You're welcome.

 

By the way, you use this shorter formula as well:

=IF(ISNUMBER(--RIGHT(TRIM(A2),1)),
TRIM(RIGHT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))),
"N/A")

It's an updated version of the formula in this link.

 

Regards