Numbers stored as text conversion

[F600314]

Hi All,

I have data in a specific column which has both numbers (which are stored as text) and text.

I want to convert the numbers which are stored as text to number format. I do not want to create separate column for doing the same.

Any way I can do the same.

Thanks

Vishal

[MarqueeCrew]

@F600314 ,

If you use a SELECT tool to change the type from text to "DOUBLE", you'll get plenty of warning messages and all of your text will change to the value of 0.  Numbers (values with 0-9 and a decimal) will be converted properly.

Cheers,

Mark

[F600314]

Hey Thanks Marquee.

I do not want my text to get converted to 0.

I want the numbers(which are stored as text) to be converted to numbers and text in that column so remain as is.

I do not want separate column as I have v lookup formulas which are looking value from this column.

Thanks

Vishal

[echuong1]

You cannot have two different datatypes in the same column. Either you have numbers stored as text, or you convert the field to a numeric value and you lose the text/words. It is not possible to "mix and match" datatypes in the same column.

If you need to perform calculations on this column and it also contains text, you may want to leave the field as a string, and use the tonumber() function in your expression to cast it as a number.

[F600314]

Hi Thanks@enchuong1.

If i use tonumber(), then it will convert only number stored as text and would not impact the existing text field?

I need to perform vlookup formula on this column.

[echuong1]

If you use tonumber() it will only convert the numeric values for the expression. Everything else will have a conversion error, so it wouldn't be factored in/impacted.

However, for vlookups that is replicated through a Join tool. Ideally you'd want to make the field that you're doing the vlookup to match in datatype. I suggest changing both to strings, and then doing the vlookup. Whether the value is a number or a string won't matter.

[rohit782192]

What is the Exact Solution. Have you try to figure out the same.