• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number required from the unstructured text

N

nikhil chordia

New Member
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.
 

Attachments

  • Polyester upload to the excel forum for help.xlsx
    438.9 KB · Views: 1
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
 
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 .
 
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.
 

Attachments

  • Ciotton.xlsx
    890.3 KB · Views: 0
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
 
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
 
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 .
 
You must log in or register to reply here.
Back
Top