Number to text
 

Through a conversion, account numbers have come over as:
10/10/2009. We want them to be 10-10-2009. However, when I use the
following formula
=LEFT(A109,2) & "-" & MID(A109,4,2) & "-" & RIGHT(A109,4)

The formula thinks the numbers (ex 10/10/2009) is a date. Formatting the
numbers to text doesn't work either. I tried adding to" ' " in front of the
numbers but I'd have to manually do it for every number.

Number to text
 

Sherry,

The following would work

=TEXT(A109,"dd-mm-yyyy") - UK date format
=TEXT(A109,"mm-dd-yyyy") - US date format

Paul

"Sherry" wrote in message
...
Through a conversion, account numbers have come over as:
10/10/2009. We want them to be 10-10-2009. However, when I use the
following formula
=LEFT(A109,2) & "-" & MID(A109,4,2) & "-" & RIGHT(A109,4)

The formula thinks the numbers (ex 10/10/2009) is a date. Formatting the
numbers to text doesn't work either. I tried adding to" ' " in front of
the
numbers but I'd have to manually do it for every number.

Number to text
 

Have you tried formatting the column that the account numbers are initially
imported into as Text before doing the data import? I believe if you do
that, then your formula will work as you think it should.

"Sherry" wrote:

Through a conversion, account numbers have come over as:
10/10/2009. We want them to be 10-10-2009. However, when I use the
following formula
=LEFT(A109,2) & "-" & MID(A109,4,2) & "-" & RIGHT(A109,4)

The formula thinks the numbers (ex 10/10/2009) is a date. Formatting the
numbers to text doesn't work either. I tried adding to" ' " in front of the
numbers but I'd have to manually do it for every number.