inserting a dash into a column of numbers
 

Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!

inserting a dash into a column of numbers
 

=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))

"pm" wrote:

Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!

inserting a dash into a column of numbers
 

LEFT(A1,5) instead of LEFT(A1,3)

"ufo_pilot" wrote:

=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))

"pm" wrote:

Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!

inserting a dash into a column of numbers
 

Thanks, but is there a way to account for the fact that there are either 1 or
2 digits to the right of the dash?

Thanks for the formulas guys...I'm way farther now than I was


"Teethless mama" wrote:

LEFT(A1,5) instead of LEFT(A1,3)

"ufo_pilot" wrote:

=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))

"pm" wrote:

Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!

inserting a dash into a column of numbers
 

Nevermind, I just sorted by digits and I got it to work by tweaking 1 or 2 in
the formula. Thanks guys!

inserting a dash into a column of numbers
 

Something like this:

=IF(LEN(A1)5,LEFT(A1,5)&"-"&RIGHT(A1,LEN(A1)-5),A1&"-")

Hope this helps.

Pete

pm wrote:
Thanks, but is there a way to account for the fact that there are either 1 or
2 digits to the right of the dash?

Thanks for the formulas guys...I'm way farther now than I was


"Teethless mama" wrote:

LEFT(A1,5) instead of LEFT(A1,3)

"ufo_pilot" wrote:

=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))

"pm" wrote:

Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!

inserting a dash into a column of numbers
 

=LEFT(A1,5)&"-"&MID(A1,6,2)
--
David Biddulph

"pm" wrote in message
...
Thanks, but is there a way to account for the fact that there are either 1
or
2 digits to the right of the dash?

Thanks for the formulas guys...I'm way farther now than I was


"Teethless mama" wrote:

LEFT(A1,5) instead of LEFT(A1,3)

"ufo_pilot" wrote:

=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))

"pm" wrote:

Hello all. I would like to insert a "-" into a column of numbers
after the
5th digit each time. The 1st 5 digits will not always be the same,
obviously
and there are either 6 or 7 digits total in each number.

I was trying to do this via find and replace, but I can't seem to
figure out
a good way.

For example: 7000111 I would like to be 70001-11

Thanks in advance!!!!