Numbers stored as text conversion
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Custom Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What is the Exact Solution. Have you try to figure out the same.
