Concatenate two fields in the same table.

Question

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)

mcfneil · Answer

Try just a List formula:List("-", [Tax ID], [Tax Number])It makes it easier to control.&nbsp;May I ask why you wanted to take the last 100 characters?

kalliegoodwin · Answer

Thanks for the recommendation.&nbsp;The 100 last characters was just me troubleshooting why it wasn't working.&nbsp;The formula you suggested is only taking part of the tax ID and none of the tax number, i.e.:&nbsp;Tax ID: 00-783-000-000Tax&nbsp; Number: 547The result is only giving me 783&nbsp;OrTax ID: 11720-000-000Tax Number: 5698The result: 11720&nbsp;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?&nbsp;

chrischris · Answer

You may have discovered this by now, but the formula has to be text to incorporate the hyphens.

kalliegoodwin · Answer

Yes, I did figure that out.&nbsp;My problem now is that I need to remove those "-". Any ideas?&nbsp;

mcfneil · Answer

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?

Forum Discussion

Concatenate two fields in the same table.

Related Content

Snapshot Fields

Can I create a single field that displays record owners from a child table

Concatenate A field based on if a checkbox is checked

Child Table Field Question

Table is missing