Forum Discussion
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" colu...
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
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
Great!!! it works perfectly. Many thanks Haytham Amairah
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 https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/.
Regards