Add a comma after the city in a Numbers column


#1

Hello,
I’m about to upload my Christmas list to create cards and the company wants me to have a comma after the city in the column that has the cities. Is there a way to automate this task? This is in Numbers on MacOS
Thanks!


#2

You could create another cell with a formula like CONCATENATE(C1; ","). If C contains your city names.

Auto fill for the whole column so you have the same for c2 etc.

Now you have a column with correct values and you can just swap them, copy/paste to the original column etc. It’s a one-time thing so wouldn’t waste too much time trying to automate that part.


#3

The concatenate solution is of course spot on as a solution to the presented question, but I feel it might also be benficial to highlight another aspect that underlies the question.

This isn’t really a data issue, but a presentation issue. I would assume from the description that some sort of mail merge process is taking place. Really the comma should be included in the mail merge template, not the data.

Why?

  1. The comma is not part of a city name. That’s encroaching into the realms of compound data (storing the data and a separator together).
  2. In terms of maintenance, it is a common value applied to all, and so having it managed in the template gives it one location to be maintained (it may be a period or semi-colon next year).
  3. If the data could be re-used for other purposes, the fact that the city is now ‘city and comma’ means either more maintenance or restriction of use.

Now, it may simply be that the comma has to be added in the data because of some contraint on the template/system, that the data will never be re-used anywhere else, that modifying the data is simply quicker and cheaper than modifying the template/system. All good, sound reasons for adopting the change the data approach at this point, But it is probbably important for anyone interested in automation to understand what the pros and cons are around systems vs. data and the implications this can have in the short term and in the future.

Hope that’s a useful thinking point for folk.


#4

The company that is asking for the info (to address my Christmas cards) asks to include the comma. I also thought it was weird.


#5

I get a syntax error with that…


#6

Try something like this…

=CONCATENATE(A1, ",")

… assuming a city in cell A1.


#7

Yay - that worked and the autofill was automatic when I dragged through the cells! Thanks so much!!! Made my day


#8

Yep, sorry — it’s “;” for regions that use “,” as the decimal separator. Which is what I’m used to.


#9

But now when I either delete the original column or copy paste it doesn’t work since in the former I get and error and in the latter I get , and "


#10

Try using Shift+Command+V. You want to paste values, not original formulas which you would get from Command+V and which would break when you start removing or overwriting content.


#11

Wow!!! Thanks that did it and now My holiday cards can be sent!!!