insert a dash
 

I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash
after the first number of any 5 digit number, and a dash after the second
digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column B

insert a dash
 

Give this formula a try...

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

--
Rick (MVP - Excel)



"Striker3070" wrote in message
...
I have a column of numbers, 5 digit up to 9 digit. I need to insert a
dash after the first number of any 5 digit number, and a dash after the
second digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column
B

insert a dash
 

And if you were looking for a VB solution, consider looping your cells using
a statement construction similar to this...

DashedText = Application.Replace(CellText, 3 + (LEN(CellText)=5),0,"-")

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Give this formula a try...

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

--
Rick (MVP - Excel)



"Striker3070" wrote in message
...
I have a column of numbers, 5 digit up to 9 digit. I need to insert a
dash after the first number of any 5 digit number, and a dash after the
second digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column
B

insert a dash
 

=if(len(a1)<=5,left(A1,1)&"-"&right(a1,len(a1)-1)
,left(a1,2)&"-"&right(a1,len(a1)-2))
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Striker3070" wrote:

I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash
after the first number of any 5 digit number, and a dash after the second
digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column B

.

insert a dash
 

You can also just format the cells:
[<100000]0-0000;00-00000

"Striker3070" wrote:

I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash
after the first number of any 5 digit number, and a dash after the second
digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column B

.

insert a dash
 

hi
since you posted in programming, perhaps a VB solution.....
Sub insertdash()
Dim r As Range, c As Range
Set r = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In r
If Len(c) 5 Then
c.Value = Left(c, 2) & "-" & Right(c, Len(c) - 2)
Else
c.NumberFormat = "@" 'in case xl can interpret as a date
c.Value = Left(c, 1) & "-" & Right(c, Len(c) - 1)
End If
Next c
End Sub

regards
FSt1

"Striker3070" wrote:

I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash
after the first number of any 5 digit number, and a dash after the second
digit if the number is longer than 5 digits.

so 12345 would need to become 1-2345
and 1234567 would need to become 12-34567

If my original numbers are in column A, I can make the new ones in Column B

.