I am trying to make a unique number to use in a comparison project. To do this, I need to concatenate two fields that are already in my table. Both fields are Text field types.
This is what I have now, but it is cutting the concatenation off.
ToText([Tax ID]) &Right(ToText([Tax Number]),100)
This is what I have now, but it is cutting the concatenation off.
ToText([Tax ID]) &Right(ToText([Tax Number]),100)
 120 Points
Posted 1 year ago
 31,438 Points
Try just a List formula:
List("", [Tax ID], [Tax Number])
It makes it easier to control.
May I ask why you wanted to take the last 100 characters?
List("", [Tax ID], [Tax Number])
It makes it easier to control.
May I ask why you wanted to take the last 100 characters?
 120 Points
Thanks for the recommendation.
The 100 last characters was just me troubleshooting why it wasn't working.
The formula you suggested is only taking part of the tax ID and none of the tax number, i.e.:
Tax ID: 00783000000
Tax Number: 547
The result is only giving me 783
Or
Tax ID: 11720000000
Tax Number: 5698
The result: 11720
How do I make it print the entire two sets of data? Regardless of the leading or ending 0's?
I should add, the formula field type I have selected is Numeric, but my two fields I am trying to concatenate are text. Does that make a difference?
The 100 last characters was just me troubleshooting why it wasn't working.
The formula you suggested is only taking part of the tax ID and none of the tax number, i.e.:
Tax ID: 00783000000
Tax Number: 547
The result is only giving me 783
Or
Tax ID: 11720000000
Tax Number: 5698
The result: 11720
How do I make it print the entire two sets of data? Regardless of the leading or ending 0's?
I should add, the formula field type I have selected is Numeric, but my two fields I am trying to concatenate are text. Does that make a difference?
(Edited)
Chris, Champion
 4,390 Points
You may have discovered this by now, but the formula has to be text to incorporate the hyphens.
 120 Points
Yes, I did figure that out.
My problem now is that I need to remove those "". Any ideas?
My problem now is that I need to remove those "". Any ideas?
 31,198 Points
I haven't tested this, but you will need to use a combination of
Right, NotRight and Left formulas to pull out the parts.
Then combine back together.
var text Left=Left([Tax ID], "");
var text Right=Right([Tax ID], "");
var text Middle=Left(NotRight([Tax ID], ""), "");
$Left & $Middle & $Right
Does that logic make sense?
Right, NotRight and Left formulas to pull out the parts.
Then combine back together.
var text Left=Left([Tax ID], "");
var text Right=Right([Tax ID], "");
var text Middle=Left(NotRight([Tax ID], ""), "");
$Left & $Middle & $Right
Does that logic make sense?
Related Categories

Formulas & functions
 2653 Conversations
 57 Followers