how to remove special characters from in a column

[mysteryboy]

Hi,

Need some help for the issue i am facing , I have a table with one of the column containing some special characters like 445$ggy,klmnft#ftt,pprtouv-op .I want to remove these special characters and need only 445,klmnft,pprtouv(want to remove $ggy,#ftt,-op) .I was using excel option "Text to Column" to remove these characters, since the data has become huge i am using access and want to perform the same condition here, but not sure how to design queries for these things , any help on this would be appreciated

Thanks and Regards,
Mysteryboy

 

[MSAccessRookie]

Hi,

Need some help for the issue i am facing , I have a table with one of the column containing some special characters like 445$ggy,klmnft#ftt,pprtouv-op .I want to remove these special characters and need only 445,klmnft,pprtouv(want to remove $ggy,#ftt,-op) .I was using excel option "Text to Column" to remove these characters, since the data has become huge i am using access and want to perform the same condition here, but not sure how to design queries for these things , any help on this would be appreciated

Thanks and Regards,
Mysteryboy

As long as you will not be wanting to keep any column values containing "$ggy", "#ftt", or "-op", then one possible solution would be to use a conditional IIf to evaluate the column.

IIf(InStr(1, TheColumn, "$ggy"), Left(TheColumn, InStr(1, TheColumn, "$ggy")-1), TheColumn)
IIf(InStr(1, TheColumn, "#ftt"), Left(TheColumn, InStr(1, TheColumn, "#ftt")-1), TheColumn)
IIf(InStr(1, TheColumn, "-op"), Left(TheColumn, InStr(1, TheColumn, "$-op")-1), TheColumn)

 

[boblarson]

Or perhaps the Replace function???

 

[twoplustwo]

Ya find "#" and replace with " "

 

[mysteryboy]

thanks for the quick reply, the data that i have has got more than 100K rows in it and they primarily contain these three special characters #,$ and - followed by other normal characters not fixed one ( means they may have 3 characters 4 or 5 characters after the special characters ) . i have to remove these special characters along with whatever other characters they carry in front ( ex sdfg#pol need to remove entire #pol and want only sdfg) ... replace function is a good option but that would only remove the special characters, pls correct me if iam wrong and is their another way of doing it

Regards,
Mysteryboy

 

[Brianwarnock]

Assuming the format if not the size of your data is constant how about writing a function containing a series of Replace statements to replace the specisl characters with , then use the Split function to create a 6 element array, then concatenate elements 1 3 5 to give you what you want.

Note that might be 0 2 4

Just a thought don't know if it will work.

Brian

 

[Brianwarnock]

This rough code

Public Function con(oldfield As String) As String

nfld1 = Replace(oldfield, "#", ",")
nfld2 = Replace(nfld1, "-", ",")
nfld3 = Replace(nfld2, "$", ",")

myarray = Split(nfld3, ",")
con = myarray(0) & myarray(2) & myarray(4)


End Function

converted your example into


445klmnftpprtouv


Hope this helps

Brian

OK not quite correct you want commas so
con = myarray(0) & "," & myarray(2) & "," & myarray(4)

should do the trick

 

[MSAccessRookie]

Or perhaps the Replace function???

DOH!
​

Why do I feel like Homer Simpson right about now?​

 

[mysteryboy]

Hi All,

Thanks for replying with diff ways of doing things,
i would not be able to use any vb based macros in this , i need access quereies or sql based ones apologies for not informing this earlier itslef
looking forward for some inputs from everyone

Regards,
Mysteryboy

 

[Brianwarnock]

Its not a macro its a function which is placed in a module and used like any other Access function in a query or SQL.

Editedfield: con(originalfield)

or

con(originalfield) as editedfield


Brian