Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
David OvellaDavid Ovella 

how to convert text to number but from the fifth value?

Hi all,
I am trying to know how to convert text to number but from the 5th value.
I know that "value(Text__c)" converts it to number
example:
Text__c: 00101100
If a use the formula "value(Text__c)" this will deploy 101.100
but I would like to deploy 100

how can I do that? any example would be realy nice

thanks
Best Answer chosen by David Ovella
Sumeet_ForceSumeet_Force
Apart from the value function, use more functions like this:
Below is just example and so you might need to adjust field names and numbers.

VALUE( MID(TEXT( VALUE(Name )), LEN(text(VALUE( AccountNumber )))-3, 3) )

All Answers

JeffreyStevensJeffreyStevens
Not sure what you mean by 5th value?

Would the LEFT, RIGHT and MID functions help you?
Sumeet_ForceSumeet_Force
Apart from the value function, use more functions like this:
Below is just example and so you might need to adjust field names and numbers.

VALUE( MID(TEXT( VALUE(Name )), LEN(text(VALUE( AccountNumber )))-3, 3) )
This was selected as the best answer
David OvellaDavid Ovella
thanks for answer Jeffrey and Sumeet

what I meant was that I would like that the formula converts text to number starting from the 5th value.
Example: 
00100152 (so the value I would like to save in this case is 152)
11228988    988
88775668    668

I tried your function Sumeet, adjusted and it works 
VALUE(MID(TEXT(VALUE(PreImpreso__c)), LEN(text(VALUE(PreImpreso__c)))-2, 10) )

for example is I enter a value with 8 numbers:
11122289 the value it convert is 289  (good)

but if I enter it with 9 numbers:
111222898 it converts to 898
and the value I would like to be is 2898

So I guess this is because of the function MID,
and I'm try to use LEFT but still can't make it work

 
Sumeet_ForceSumeet_Force
There has to be a criteria based on which the formula will function.So if you are aware of the different lengths of any number that can come in , then you can have CASE function (along with LEN function on your field)  and use above formula based on different lengths to generate different return values.
David OvellaDavid Ovella
this is the formula I'm using and is working well


IF(OR(ISBLANK(PreImpreso__c),!ISNUMBER(PreImpreso__c)),0, 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==5, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),1)), 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==6, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),2)), 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==7, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),3)), 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==8, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),4)), 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==9, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),5)), 

IF(LEN(TEXT(VALUE(PreImpreso__c)))==10, 
VALUE(RIGHT(TEXT(VALUE(PreImpreso__c)),6)),0 

)))))))

thanks