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

Formulas and Functions

Haytham Amairah

Aug 21, 2019

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

Hope that helps

Extract numbers from text.xlsx9 KB

3 Replies

Haytham Amairah
Silver Contributor
Aug 21, 2019
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")

Hope that helps
Extract numbers from text.xlsx9 KB
- José Lorenzo Strédel García
  Copper Contributor
  Aug 21, 2019
  Great!!! it works perfectly. Many thanks Haytham Amairah
  - Haytham Amairah
    Silver Contributor
    Aug 21, 2019
    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 https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/.
    
    Regards

Forum Discussion

Help with Excel´s formulas