Concatenate Text Fields

[JasonHik]

I am hoping that a learned mind on this forum can assist me with the
following text formula query.

I am using an add-on to MS Project that requires me to create a text
field and populate it with data in the following format (numeric
examples given);

123456:345678
Startchainage:Endchainage

I currently have 2 number fields in MS Project as follows;
Start Chainage = custom number field [NUM1];
Finish Chainage = custom number field [NUM2];
I use the start and finish fields for other calculations so I need
those columns.

I have tried create a custom text field formula and Concatenate using
[num1]&":"&[num2] but this produced a #error response.

Does anyone have any advice regarding how I can Concatenate [num1] &
[num2] and insert a colon":" in the middle to produce
Startchainage:Endchainage in a text field?

Best Regards and My Thanks in Advance

Jason

PS: This post is a repeat on a different (and incorrect) thread. I'm
having trouble deleting the original so please bear with me.

 

[Rob Schneider]

Json,

The "&" character will concatenate strings, but your [num] fields are
numbers, not characters. So what you have to do is convert the numbers
to stings, and then concatenate, e.g. something like:

str([num1])&":"&str([num2])

where the str() function is used. You can read more about this str()
function, and other functions, in Help.


--rms

www.rmschneider.com

 

[JasonHik]

Thanks Rob,

The string() function is MS Project does not appear to be the righ
function, but taking your lead I used the following;

Left([Number1],6) & ":" & Left([Number2],6)

which appears to work.

Many thanks for the tip on the number being a string.

Jaso

 

[JulieS]

Pardon me for bumping in, but I was able to get the formula:

[Number1] & ":" & [Number2]

to work without difficulty and without the need to convert to a
string in both Project 2003 as well as Project 2007. The formula is
written in a text field. Is it possible that your reference to
[Num1] in your original formula was not referring to the correct
field name? There are no fields named [Num1] in Project only
[Number1].

There is nothing wrong with your formula as long as the number
strings don't exceed 6 characters, if they do you'll need to
remember to edit the formula.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Rob Schneider]

Really weird and unexpected. Learned something today. How the Left()
function--which is supposed to work only on strings--and the str()
function--which is supposed to work only on numbers--all works is a big
mystery.

I just took it for granted because you said it did not work
(concatenating a string to a number in VBA) that it would not work. The
fact that it does work (as Julie noticed and I just tried here) begs the
question on why it didn't work for you.

What happens when your "numbers" which look to be strings are less than
or more than 6 characters?


--rms

www.rmschneider.com

 

[JasonHik]

Makes me wonder if I had the syntax correct for my original formula.
The field names [num1], [num2] were my own descriptions for the sake of
this forum- I definitely had them pointing at the correct MS Project
fields.

Thanks Julie.

 

[JasonHik]

UPDATE

I must have had the syntax wrong. Works a treat now.

This is a pretty good forum methinks!

 

[JulieS]

You're welcome Jason.