Delete invalid filename characters

[paulked]

Hi all

I need to remove invalid characters from cells.

Cells G7:G10 contain up to 2 characters each and G12 contains a concatenation of these.

Cells I7:I10 contain up to 2 characters each and I11 contains a number, I12 contains a concatenation of I7:I11.

The Cells G7:G10 and I7:I11 are read in from an external source (A PLC in this case) and if they are not used by the PLC they can contain any weird character.

For example:

G7 = "EA", G8 = "SK", G9 = "Y-", G10 = "13" & therefore G12 = "EASKY-13". This is the directory I want to save the file in.
I7 = "BU", I8 = "BX", I9 = "J", I10 ="", I11 = "551" & therefore I12 = "BUBXJ551". This is the filename of the sheet I want to save.

But, in reality, as only one character of I9 and none of I10 are used get unusual characters in place of blanks. This causes the VBA to crash as the file cannot be saved.

:help Is there a way to ged rid of any invalid characters from these cells before saving the file?

Many thanks

Paul Ked

[Bob Phillips]

Try this

=SUBSTITUTE(I7&I8&I9&I10&I11,CHAR(1),"")

[paulked]

Try this

=SUBSTITUTE(I7&I8&I9&I10&I11,CHAR(1),"")

Many thanks, it seems to do the trick :thumb

[paulked]

Thanks xld, but I'm still getting the occasional error (the last one was what appeared to be an "h" (lower case aitch)).

How do I delete all but ASCII 65-90 (ie only allow upper case letters A-Z)

Many thanks in anticipation

Paul Ked

[Bob Phillips]

You would ave to use VBA for hat.

[paulked]

gone searching :reading:

[pvanrooijen]

ASAP utilities (free version) can do the trick.

[paulked]

ASAP utilities (free version) can do the trick.

Thanks for that. I tried it, but it doesn't do it automatically. I'm going to have to struggle witth VBA.

[pvanrooijen]

Hello Paul

Yes realised later when I took a deeper look at the problem.
With advanced character removal from ASAP it is possible only will take to much time after all. Then manual removal would be much quicker.

Hereby a routine I once used.
Original routine by Allen Wyatt (VitalNews nr T003219)
I modified it for your use:



Sub CleanText1()
'modified from original routine by Allen Wyatt
'T003219_Getting_Rid_of_Alphabetic_Characters

Dim rngCell As Range
Dim intChar As Integer
Dim strCheckString As String
Dim strCheckChar As String
Dim intCheckChar As Integer
Dim strClean As String
For Each rngCell In Selection
strCheckString = rngCell.Value
strClean = ""
For intChar = 1 To Len(strCheckString)
strCheckChar = Mid(strCheckString, intChar, 1)
intCheckChar = Asc(strCheckChar)
Select Case intCheckChar
Case 0 To 64
Case 91 To 255
Case Else
strClean = strClean & strCheckChar
End Select
Next intChar
rngCell.Value = strClean
Next rngCell
End Sub


Regards
Atmaram

[paulked]

That looks good. I'm away on a Rugby tour for 5 days and I will try it when I return... If I return in one piece!

Sometimes even the weakest have a strong point ;-)