Aug 21 2019 07:17 AM
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 |
Aug 21 2019 08:05 AM
Solution
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")
Hope that helps
Aug 21 2019 08:12 AM
Great!!! it works perfectly. Many thanks @Haytham Amairah
Aug 21 2019 08:16 AM - edited Aug 21 2019 08:18 AM
Aug 21 2019 08:16 AM - edited Aug 21 2019 08:18 AM
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
Aug 21 2019 08:05 AM
Solution
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")
Hope that helps