how to read column with mix type

[Lingbin]

Hi,
I am new to use KNIME. need some help.
somehow my source data which storage numbers as text, also due to some mistake input, the value may mix with certain string. so I create some data set to demon how string to number node can help me clean the data.
here is some findings:

1. If a column 100% number format, then whatever format the number storage as, example negative number -1290 view as (1290). XLS reader node can read it correctly as real number.
2. if column 100% number but storage as text format, see example column B, CELL B4-B6, KNIME can recognize few of them with string to number node, which still have issue of read negative number with special format, example,(1290)… Queston1: how fix it.
3. If column accidently mix with some real string data, see example column C, XLS reader node read C6 as string while which is the real number but with special format, string to number node also cannot convert it correctly. similar case happen when it storage as %, see column G. with check re-evaluate formula in XLS reader can only solve column C, but column G issue remain. Question 2: how to fix it.
   In the end the question 1 and 2 may comes to same question: how convert those number like string to number.
   Thanks.
   
   
   
   
   
   
   
   

[mlauber71]

Welcome to the KNIME community.

You could try to select all relevant string columns and replace ()% with nothing and then convert strings to numbers.

String Replacer — NodePit

Question is if 100000% would need some special treatment (like being divided by 100) because numbers are confused.

You could store the information that there was a % in an extra column and do a math operation later.

Rule Engine — NodePit

Math Formula — NodePit