Numbers on Mac: Find and replace only in one column

Numbers on Mac: Find and replace in one column

Today I had the opportunity to get a little smarter myself. The reason for this was a CSV file from my wife, which had listed all euro amounts in a column, but with a point "." were separated and therefore were not recognized as a currency amount by the German Numbers (Mac version). Accordingly, he refused to add a total of the values ​​in the column.

We quickly found out how to solve the problem: You perform a "search and replace" and look for points and replace them with commas. Once you have done this, the total will also be displayed correctly. But…

Unfortunately, Numbers on the Mac does not offer an option to limit the search replace function.

Unfortunately, Numbers on the Mac does not offer an option to limit the search replace function.

Limitation to one column is not possible

Unfortunately, there were other columns in our table that also contained points. These were among other things in the date, in article descriptions and similar fields. Finding and replacing, however, should only be limited to the one column with the currency.

Actually, this is unfortunately not technically possible with Numbers on the Mac, because there is no option with which one could narrow down the search and replace. The function simply always affects the complete worksheet with all columns and all values.

Update 26.6.2020/XNUMX/XNUMX: Define decimal separator in import settings

As a reader (thank you, Christian!) Told me earlier, you can also solve the problem by calling up the import settings and entering the point as a decimal separator. I've just tried this and lo and behold: Numbers automatically converts all points into commas if, in his opinion, it could be a number with a comma.

When you have opened the CSV file, a small message appears at the top right that you can change the import settings. If you click on this, you get to a window with "Advanced Settings". If you expand this, you will find a line with the name "decimal point". There you enter a point and click on "Update table".

After opening a CSV file, Numbers offers the option to change the import settings.

After opening a CSV file, Numbers offers the option to change the import settings.

 

The decimal point determines the character with which the comma numbers work - with a point or a comma.

The decimal point determines the character with which the comma numbers work - with a point or a comma.

Solution: hide all other columns

After a bit of trial and error, however, we found a way to only edit the one column.

The procedure is roughly as follows: You hide all columns except for those in which you want to search and replace. Then you carry out the processing and only this one column that is still visible is affected. After the work is done, all columns are shown again. How this works exactly follows in three screenshots and below in a list of the individual steps in text form.

Step 4 (see list of individual steps below): Hide all columns except for the ones we want to edit.

 

Step 7: Now the search and replace command is executed on this one column.

 

When you have finished editing, you have to show all columns again.

Step 9: When you have finished editing, you have to show all columns again.

Here are the step-by-step instructions:

  1. You mark all the columns using the bar that says A, B, C, D etc.
  2. Now hold down the CMD key and click on the column that you want to edit (in my case C).
  3. All but one of the columns should now be marked.
  4. Now click with the right mouse button in one of the marked columns on the letter in the top bar (for me this is column D, for example).
  5. In the menu that opens, select the item "Hide selected columns".
  6. Now only the one column that we want to edit should be visible.
  7. With the key combination CMD + F we call the function "Search and Replace" and carry out our replacement request. In my case it was find a period and replace it with a comma.
  8. When all the replacements have been made, we right click on the head of the column (in my case on the letter C).
  9. In the open menu we now select "Show all columns".
In the finished table you can see that the points have only been replaced by commas in the column marked in red.

In the finished table you can see that the points have only been replaced by commas in the column marked in red.

If everything went well, the search and replace should only have affected the one column. In my case everything worked. I did the thing on my MacBook Pro under macOS Catalina with Numbers 10.0. However, it is possible that the menu items in other macOS versions or other Numbers versions do not look the same as in my screenshots.

-
 

 

Effectively for free: iPhone 13 Mini and iPhone 13 deals with top conditions at Otelo - Advertisement

2 comments

  1. Christian says:

    You can also specify what the decimal point is when importing CSVs. Just enter a point.

    • Sir Apfelot says:

      Hello Christian! Thanks for the tip. That works too, great! I just tried it ... I'll add that again in the article above.

Leave a Comment

Your e-mail address will not be published. Required fields are marked with * .