SOLVED

# Help with Excel´s formulas

Highlighted
Occasional Contributor

# Help with Excel´s formulas

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
Highlighted
Solution

# Re: Help with Excel´s formulas

@José Lorenzo Strédel García

Hi,

`=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")`

Hope that helps

Highlighted

# Re: Help with Excel´s formulas

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

# Re: Help with Excel´s formulas

@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