Formula modifying an existing set up numbers
Hi Everyone, I have a formula I am trying to find but I'm losing hope. I need to remove a decimal in a string of numbers and reformat in a specific way. Regardless of the original length, I need to ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendNothing like a little brainteaser in the morning.
try this
var number Input = [Raw Number]; // insert your field name here
var number Integer = Int($Input);
var number Decimal = $Input - $Integer;
var text FirstFour = PadLeft(ToText($Integer),4,"0000");
var text LastFive = PadRight(Left(SearchAndReplace(ToText($Decimal),"0.",""),5),5,"00000");
$FirstFour & $LastFive
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
THANK YOU! It's ALMOST working! But right at the end, $FirstFour & $LastFive is highlighted saying a number was expected but found text. Maybe I need to change the field type? I have formula numeric
------------------------------
Jamie Strauss
------------------------------
- MarkShnier__You
The field type needs to be formula text because it has leading zeros and trailing zeros.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------Mark! Worked like a charm!! Thank you so much! Now, any ideas on how to export it so excel doesn't drop all of the zeros?
------------------------------
Jamie Strauss
------------------------------- MarkShnier__You
I don't know a way to have the Excel CSV open up and maintain the leading zeros. The only way I caan think of would be to change the last line to this
"#" & $FirstFour & $LastFive
Then Excel would see that as a text string. Any non numeric value in the string will cause Excel to treat it as text - there is nothing special about using an #.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------