Using Split Function to add a hyphen to telephone number

[met1713]

I am trying to use a split function but it keeps saying subscript out of range. The input is 1234567 (any 7 digit number)
and the output needs to be 123-4567. so a user can enter their telephone number but it will add the hyphen for them. I can get it split when it has spaces but not without spaces. Need help

 

[Rick Rothstein]

I am trying to use a split function but it keeps saying subscript out of range. The input is 1234567 (any 7 digit number)
and the output needs to be 123-4567. so a user can enter their telephone number but it will add the hyphen for them. I can get it split when it has spaces but not without spaces. Need help

You wouldn't use the Split function for this, simply use the worksheet's REPLACE function instead...

Number = 1234567
HyphenNumber = WorksheetFunction.Replace(Number, Len(Number)-3, 0, "-")

The above assumes you phone number could possibly be in this form... (123)4567890 as well as 1234567, but if it will always be 7 digits long, then use this instead...

Number = 1234567
HyphenNumber = WorksheetFunction.Replace(Number, 4, 0, "-")

 

[gaz_chops]

As a formula

=REPLACE(A1,4,,"-")