Data Validation and Formatting Question

[LLL]

I am attempting to import a excel file into a new file that has certain criteria for the cells and text length. Might any of you know how I can import the file and have it automatically delete any letters or spaces that do not meet the criteria for those cells?


Also in formatting cells with phone numbers, I only want them to look this way ###-###-####. I have tried numerous things but it will still not change those that may be formatted this was (###)###-####. Any thoughts?

Thanks

[mumin_abdul]

Could you give us an example of what you mean?

You can use Trim() to remove spaces at the beginning and end of strings

[mumin_abdul]

The best way to carry out your first request would be to just import the data 'as-is'. Then you can run a search going through each column's rows and manipulate the data as you want.

[mumin_abdul]

Dim strString As String
strString = " ab cd defgh jk "
strString = Replace(strString, " ", "")
MsgBox (strString)

The above seems to remove ALL spaces in a string.

Just replace " " with the character/string you want to remove e.g.



'This removes the string "moo" wherever it finds it in the string variable strString
Dim strString As String
strString = " ab cd defgh jk "
strString = Replace(strString, "moo", "")
MsgBox (strString)


or



'This removes the character "a" wherever it finds it in the string variable strString
Dim strString As String
strString = " ab cd defgh jk "
strString = Replace(strString, "a", "")
MsgBox (strString)

[Gingertrees]

This is close to what I would like for my project. I'd like to leave some flexibility in the phone field, but I'm tired of seeing 555/222/1111 etc. I'm not good with syntax yet, so this is all I have (and it's not erroring...or doing anything)

Private Sub PhoneFix(ByVal Target As Range)
Dim strPhone As String
If Target.Column <> 9 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Not Null Then
strPhone = "(aaa)aaa/aaaa"
Replace(strPhone, "(", "") = strPhone
And Replace(strPhone,")","-") = strPhone
With Replace(strPhone, "/", "-") = strPhone
End If
End If
End Sub

help please?

[mumin_abdul]

Try this quick fix:


Private Sub PhoneFix(ByVal Target As Range)
Dim strPhone As String
If Target.Column <> 9 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If IsNull(strPhone) = False Then
strPhone = "(aaa)aaa/aaaa"
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone,")","-")
strPhone = Replace(strPhone, "/", "-")
End If
End Sub

[Gingertrees]

My thought is that code should change something like (999)222/3333 to 999-222-3333. But it does not make any changes. ???

[CHatmaker]

Re; Phone Number. If it's just formatting use: Selection.NumberFormat = "###-###-####"

If the data actually has the formatting characters embedded, then the replace method suggested earlier is probably the way to go.

[mumin_abdul]

Maybe this following will work.



Dim strPhone As String
If Target.Column <> 9 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
strPhone = Target.Text
If IsNull(strPhone) = False Then
strPhone = "(aaa)aaa/aaaa"
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "-")
strPhone = Replace(strPhone, "/", "-")
End If


If not, perhaps you wont mind posting an excel file as an example?

[Gingertrees]

OK, I'm willing to sacrafice my leniency. How about we make it so the user HAS to enter 800-555-1212 and forget the parentheses all together. I realized I also need a similar thing for Social Security Number (SSA) in my worksheet.

So I need column #9 formatted for 800-555-1212 (phone), and
column #17 formatted for 333-22-1111 (SSA number)

In both of these examples, I want the user to be halted if they enter a /, \, (), ~, etc. Just hyphens, please.

...and nowhere can I find a Data Validation formula to do this for me, so I must turn to VBA. Help?

[CHatmaker]

Use data validation to restrict user to entering only numbers from 0 to 9999999999. Let formatting add dashes. 999-999-9999. No coding. Saves your users keystrokes