Replace Illegal Characters Assistance

[gbc123]

Hi,

I have an output, e.g column A, which contains a text output but also characters that I want to get rid of e.g *,;*:,/ as well as the presence of any tabs or carraige returns.

Sample text (A1) shows exactly as

Mr A Smith
13 Jones Road;
Bicester,BS11

I need to change this to (into column B) as
Mr A Smith 13 Jones Road Bicester BS11

I have searched in vain and time is now against me.

Any help gratefully received.

Thanks.

G.

 

[SteveO59L]

Have a look at Transpose()

 

[onlyadrafter]

Hello,

how about starting with

=SUBSTITUTE(A1,CHAR(10)," ")

then maybe doing a manual find and replace, if you're in a hurry?

 

[Richard Schollar]

Hi

Here's a VBA solution using a UDF - needs to be pasted into a standard module in your workbook:

Function StripNonAlphaNums(ByVal strRef As String) As String
Dim b() As Byte, i As Long, temp As String
b = StrConv(strRef, vbFromUnicode)
temp = ""
For i = LBound(b) To UBound(b)
    Select Case b(i)
        Case 97 To 122, 65 To 90, 48 To 57, 39, 32
            temp = temp & ChrW$(b(i))
        Case 10
            temp = temp & " "
    End Select
Next i
StripNonAlphaNums = temp
End Function

Use like a normal function:

=StripNonAlphaNums(A1)

Best regards

Richard