Select to view content in your preferred language

Field Calculator Combining Fields

1221
5
08-06-2013 02:18 AM
by Anonymous User
Not applicable
Original User: M.P.N.Mark_RUG

Good day all,

I've got a question about the field calculator. I would like to combine several fields together in one, new field. (Street name, House number, Extension, Postal Code and Place). So my code looks like this (the fieldsname are in Dutch)

`[Straatnaam]&" "&[Huisnummer]&[Huisletter]&" "&[Postcode]&" "&[Woonplaats]`

After a couple of minutes, I get the result in my newly created field. However, not every streetname has a extension (Huisletter) and is left 'blank' in its original field, which results in an extra space in my newly created field.
How do I modify my code to include the number, but exclude the 'blank' cells?

Tags (4)
1 Solution

Accepted Solutions
MVP Honored Contributor
Good day all,

I've got a question about the field calculator. I would like to combine several fields together in one, new field. (Street name, House number, Extension, Postal Code and Place). So my code looks like this (the fieldsname are in Dutch)

`[Straatnaam]&" "&[Huisnummer]&[Huisletter]&" "&[Postcode]&" "&[Woonplaats]`

After a couple of minutes, I get the result in my newly created field. However, not every streetname has a extension (Huisletter) and is left 'blank' in its original field, which results in an extra space in my newly created field.
How do I modify my code to include the number, but exclude the 'blank' cells?

Here is the easy way to do it.

`[Straatnaam] & " " & Trim([Huisnummer] & [Huisletter]) & " " & [Postcode] & " " & [Woonplaats]`
5 Replies
by Anonymous User
Not applicable
Original User: mdenil

It seems that Huisletter sometimes contains a space character instead of an empty string.
A true empty string would not add space to your concatenated string

In your pre-logic block, test Huisletter for a single space
If it is a space set a variable to an empty string
if Huisletter is anything else, assume it is the actual Huisletter, and set the variable to the Huisletter value.

now build your concatenated string using the variable instead of Huisletter.
MVP Honored Contributor
Good day all,

I've got a question about the field calculator. I would like to combine several fields together in one, new field. (Street name, House number, Extension, Postal Code and Place). So my code looks like this (the fieldsname are in Dutch)

`[Straatnaam]&" "&[Huisnummer]&[Huisletter]&" "&[Postcode]&" "&[Woonplaats]`

After a couple of minutes, I get the result in my newly created field. However, not every streetname has a extension (Huisletter) and is left 'blank' in its original field, which results in an extra space in my newly created field.
How do I modify my code to include the number, but exclude the 'blank' cells?

Here is the easy way to do it.

`[Straatnaam] & " " & Trim([Huisnummer] & [Huisletter]) & " " & [Postcode] & " " & [Woonplaats]`
by Anonymous User
Not applicable
Original User: dmhoneycutt

For everyone's future reference, there's a blog post about combining (concatenating) fields:
Concatenating field values using Calculate Field
MVP Esteemed Contributor
Here is the easy way to do it.

`[Straatnaam] & " " & Trim([Huisnummer] & [Huisletter]) & " " & [Postcode] & " " & [Woonplaats]`

I think it's even easier in Python. I am probably biased though...

```"{0} {1} {2} {3}".format(
!Straatnaam!, (!Huisnummer! + !Huisletter!).strip(), !Postcode!, !Woonplaats!)```
by Anonymous User
Not applicable
Original User: M.P.N.Mark_RUG

Here is the easy way to do it.

`[Straatnaam] & " " & Trim([Huisnummer] & [Huisletter]) & " " & [Postcode] & " " & [Woonplaats]`

Well, sometimes the easiest way is the best way. I forgot about adding the trim function. Thank you very much.

Also PMdmhoneycutt, thank you for that blog. I saw some excellent stuff there.