Excel: Turn numbers exported from ACAD into European Format

There is a quick way to turn numbers in Excel into European format and to remove apostrophes.
Català - Castellano - Deutsch
European Number Format, uses a coma as a decimal separator instead the period of the English format. When exporting data from AutoCAD to Excel using the DATAEXTRACTION command, the numbers exported are in English format, so to transform them to the European Format we need to replace the points for comas. This can very done very easy by using the find and replace function in Excel. Type CTRL + F or click the Replace button on the Editing area of the Home Tab (Excel 2007). Find Points and replace them by comas, and select Replace All.

There is an extra step needed if we want to be able to use the data from AutoCAD in Excel as numbers. All the Cells exported from CAD start with an apostrophe ('). See below.

This makes the numbers not usable as such. Luckily there is a trick to batch remove this sign. If you have a column with numbers, insert a new column to its right. On the cell next to the first number in the row enter the follwing fórmula.

Where D2 is the Cell with the numbers with the apostrophe you need to eliminate. Then copy the cell next to all numbers you want to transform. You will see now the result of the formula as the number you want to use. Something like this:

The left cell is the one still containing the apostrophes, and that is why the numbers are aligned to the left. The one on the right is the result of the formula we have applied.

A final step I would recommend is to copy all these new cells and paste them in a new column using Paste Special. Paste Special can be accessed after copying some cells by right clicking on a cell. Then On the Paste Special Window Select "Values".

That's it, now you can delete the other columns (the one with the apostrophes and the one with the formulas, and you have just the new column with plain numbers. The explanations is long, but the process takes lest than a minute.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...