Hi All:

I was wondering if one of you Excel guru's out there could coach me
through this challenge I have. I run an extract report from our
business system into Excel and I need to have a particular column of
data changed as follows:

From: 06001 To: 06-001

Is there a formula of some sort that I could write to handle inserting
the hyphen "-" in between the 2nd and 3rd character of my data column
to accomplish this?

Please help.

Thanks,

Big Cat

Hi,

As long as it's always in between second and third characters this should
work:

=REPLACE(A1,3,0,"-")

where A1 contains the original text

On 23 Jul 2006 22:52:22 -0700, "Big Cat" wrote:

Hi All:

I was wondering if one of you Excel guru's out there could coach me
through this challenge I have. I run an extract report from our
business system into Excel and I need to have a particular column of
data changed as follows:

From: 06001 To: 06-001

Is there a formula of some sort that I could write to handle inserting
the hyphen "-" in between the 2nd and 3rd character of my data column
to accomplish this?

Please help.

Thanks,

Big Cat

1. You could custom format the column:
Format/Cells/Number/Custom Type: 00-000

2. You could use the formula:

=TEXT(A1,"00-000")


--ron

Ron,

Thanks for the help. I found it easiest to go with option 1 to solve
my problem immediately. If I wanted to use option 2 (formula), where
would I insert the formula in the list?

Please advise.

Thanks,

Big Cat

Ron Rosenfeld wrote:
On 23 Jul 2006 22:52:22 -0700, "Big Cat" wrote:

Hi All:

I was wondering if one of you Excel guru's out there could coach me
through this challenge I have. I run an extract report from our
business system into Excel and I need to have a particular column of
data changed as follows:

From: 06001 To: 06-001

Is there a formula of some sort that I could write to handle inserting
the hyphen "-" in between the 2nd and 3rd character of my data column
to accomplish this?

Please help.

Thanks,

Big Cat

1. You could custom format the column:
Format/Cells/Number/Custom Type: 00-000

2. You could use the formula:

=TEXT(A1,"00-000")


--ron