Number required from the unstructured text

[nikhil chordia]

Hi Team,

I require help in extracting the number in the attached file under column M . Column M has currently unstructured data , a mix of characters and numbers. Additional condition is that the numbers in a particular box must be minimum 2 digit and more (for example, if the text is 125(1 , the value i would need is only 125 and not the "1" or the "(". Also any number that is further separated with a special character should not be considered. I am using excel 2010 so unable to use the formula =IFERROR(_xlfn.NUMBERVALUE(RIGHT(M2,3)),_xlfn.NUMBERVALUE(RIGHT(M2,2))) or for example this : =_xlfn.NUMBERVALUE(LEFT(N29,3))

Could someone please help me on this ? I have attached the file as is.

 

[NARAYANK991]

Hi Nikhil ,

There is a lot of variation in the data in column M.

Can you say what should be the result for a data item 0.10 , 0.07 , 85.0 ?

Narayan

 

[nikhil chordia]

Hi Narayan,

Firstly thanks for the help. Basically the value that is being pulled in column M named "LEFT" and Column N named "Right" are connected to the column C . What i really want is the GSM value from Column C. As you can see the GSM value is currently unstructured and at places it precedes and at places it after GSM in the data. I hope this background helps in solving this issue. Let me know .

 

[nikhil chordia]

I have also attached a similar file for which my colleague was able to derive the data, but i am unable to do so. For your reference, attaching that file.

 

[NARAYANK991]

Hi ,

I am sorry but I cannot help.

You need to eliminate the variation in your data.

Otherwise list down all the non-numeric characters in your data , which need to be eliminated , so that either a formula or VBA can be used.

I have already noticed characters such as : , comma , G , W , even a number such as 1091 from which 109 is to be extracted !

It is your job to list down all of this , and then specify it in your problem description.

Hopefully someone else will help out.

Narayan

 

[prasaddn]

Hi,

At the original post, if the question is a alternate function for numbervalue()? then instead of:
IFERROR(_xlfn.NUMBERVALUE(RIGHT(M2,3)),_xlfn.NUMBERVALUE(RIGHT(M2,2)))
The revised formula will be =IF(ISNUMBER(RIGHT(M9,3)/1),RIGHT(M9,3),RIGHT(M9,2))

And for example : =_xlfn.NUMBERVALUE(LEFT(N29,3))
simply use: Left(N29,3)

The challenge I believe you may have is your friend has applied different formulas for same column O, "Extracted", based on scenarios of output in left and right named column.

Regards,
Prasad DN

 

[nikhil chordia]

Hi Prasad,

Thanks for all your help and inputs, the formula suggested doesnt work in this case as the result should be only the number and not special characters. Also any idea on why the xlfn function is throwing a #NAME error when applied to a similar data on my side ? I am using excel 2010 right now, would that be a problem ? I googled info on this, but that dint help either. Let me know if you can still help in any way possible .

 

[NARAYANK991]

Hi Nikhil ,

See this link :

https://support.office.com/en-sg/ar...-formula-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025

The NUMBERVALUE function is available in Excel 2013 and Excel Online versions only.

Narayan