SOLVED

Help with Excel´s formulas

Copper 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
best response confirmed by José Lorenzo Strédel García (Copper Contributor)
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

1 best response

Accepted Solutions
best response confirmed by José Lorenzo Strédel García (Copper Contributor)
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

View solution in original post