At 14:29 09/07/2008 +0200, Al Noname wrote:
I have a case where numbers are used in a text format of xxxxxxxxxx
(10 characters), that should be converted to xxx.xxx.xxxx (10
characters in groups of 3, 3 and 4 separated by a dot). The
characters should be text, but using numbers; e.g. 001.002.0003 as a
part number for a machine.
In an exported CSV file there is a number of e.g. 0010020003 without
the dots. The export was from a database that could not accommodate
the numbers with the dots, because the fields were limited to 10
characters. I now need to format the cells as text (for CSV) with
dots added, to import into a new database.
When I use @###.###.#### it just adds ###.###.#### behind the
number, e.g.: 0010020003###.###.####. I have tried various
work-around's with numbers to text etc. What I need is to convert
0010020003 to 001.002.0003 as text.
At 17:29 09/07/2008 +0200, Al Noname wrote:
Actually in the cell it is '0440221701. If I remove the ' in front
of the text '0440221701, it formats correctly when it is a number
with 1 leading zero.
How can I remove all the leading ' tags for text in the leading zero
cells? Find-Replace does not work, even for Regular Expressions.
Since you have text values in the cells, converting them to numbers
is probably going in the wrong direction. Instead, create a new
column with the values actually as you wish to see them
exported. One way to do this would be:
=LEFT(x;3)&"."&MID(x;4;3)&"."&RIGHT(x;4)
This takes the leftmost three characters, the three central
characters starting at character four, and the rightmost four
characters, and concatenates them with the two dots in between.
This column will output to CSV as you need.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
