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 12345
and 1234567 would need to become 1234567
If my original numbers are in column A, I can make the new ones in Column B


0




Reply

Striker3070

5/5/2010 6:00:50 PM 

Give this formula a try...
=REPLACE(A1,3(LEN(A1)=5),0,"")

Rick (MVP  Excel)
"Striker3070" <striker3070@qwest.net> wrote in message
news:37978E49438C41759F618C87A5486857@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 12345
> and 1234567 would need to become 1234567
>
> If my original numbers are in column A, I can make the new ones in Column
> B


0




Reply

Rick

5/5/2010 6:17:30 PM


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:37978E49438C41759F618C87A5486857@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 12345
>> and 1234567 would need to become 1234567
>>
>> If my original numbers are in column A, I can make the new ones in Column
>> B
>


0




Reply

Rick

5/5/2010 6:32:04 PM


=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 12345
> and 1234567 would need to become 1234567
>
> If my original numbers are in column A, I can make the new ones in Column B
>
> .
>


0




Reply

Utf

5/5/2010 6:56:01 PM


You can also just format the cells:
[<100000]00000;0000000
"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 12345
> and 1234567 would need to become 1234567
>
> If my original numbers are in column A, I can make the new ones in Column B
>
> .
>


0




Reply

Utf

5/5/2010 6:57:01 PM


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 12345
> and 1234567 would need to become 1234567
>
> If my original numbers are in column A, I can make the new ones in Column B
>
> .
>


0




Reply

Utf

5/5/2010 7:01:01 PM



5 Replies
1170 Views
(page loaded in 0.323 seconds)
