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 

Give this formula a try...

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

-- 
Rick (MVP - Excel)



"Striker3070" <striker3070@qwest.net> wrote in message 
news:37978E49-438C-4175-9F61-8C87A5486857@microsoft.com...
> 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 

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:#K8uh9H7KHA.4208@TK2MSFTNGP06.phx.gbl...
> Give this formula a try...
>
> =REPLACE(A1,3-(LEN(A1)=5),0,"-")
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "Striker3070" <striker3070@qwest.net> wrote in message 
> news:37978E49-438C-4175-9F61-8C87A5486857@microsoft.com...
>> 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
> 

=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 
> 
> .
> 

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 
> 
> .
> 

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 
> 
> .
>