From: Striker3070 on
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

From: Rick Rothstein on
Give this formula a try...

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

--
Rick (MVP - Excel)



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

From: Rick Rothstein on
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(a)NO.SPAMverizon.net> wrote in message
news:#K8uh9H7KHA.4208(a)TK2MSFTNGP06.phx.gbl...
> Give this formula a try...
>
> =REPLACE(A1,3-(LEN(A1)=5),0,"-")
>
> --
> Rick (MVP - Excel)
>
>
>
> "Striker3070" <striker3070(a)qwest.net> wrote in message
> news:37978E49-438C-4175-9F61-8C87A5486857(a)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
>
From: Gary Brown on
=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
>
> .
>
From: Bob Umlas, Excel MVP on
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
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: ScreenUpdating
Next: Lookup Table