Topics Search

Replace() Function And Wildcards

Replace() Function And Wildcards
Views: 48
In a recent posting several solutions were provided for removing unwanted characters from a string. Perhaps in my own stupidity I thought that wildcards could be used in conjunction with the Replace() function to eliminate unwanted characters. I've played with this for hours and have yet to come up with a working solution. Here's an example of what I'm trying to do -- with wildcards:

x = "1 800 123-4567"
? replace(x, [!0-9], "")

This obviously doesn't work as advertised. I'm not looking for a function that loops thru each character in a string. I've already got that. I'm looking for a one-liner where it's somehow possible to use wildcards in conjunction with the Replace() function, with a working example.

If you've got a working example, I'd sure love to see it, elsewise tell me why it's not possible.

I appreciate your help.

Best wishes - Bob
Sponsored Links:

More topics


Replace hash (#) with query

I have been having this problem with trying to replace hashes from a field for a little while. I know that they are wildcards for number in queries, but still, the usual brackets approach doesn't seem to help when within a replace function.

The two main failed attempts have been run an update query in which the field is updated to the following:

a) Replace([Age],Chr(35),"")
b) Replace([Age],"[#]","")

Neither one of which has replaced any hashes.

Repeat Replace Function

I have small requirement with Replace function, as per my understanding Replace function is used to replace a text in a filed.

My requirement is if one filed contians more than 10 words to replace at once, how to repeat the sam function for that filed to give output in another filed.

Field1 Exp1: NewName
A, B,C,D,E,F Apple,Bat,Cat,Dog,Eat,Frog.

Use the Find and Replace dialog box to change data

This topic explains how to use the Find and Replace dialog box to find and optionally replace data in an Access database.
* Learn about using the Find and Replace dialog box
* Find and replace data in a table
* Find and replace data in a form
* Find data in a query result set
* Find wildcard characters
* Examples of wildcards in use
* Find quotation marks and null or blank values
* Find and Replace dialog box control reference

Using other records in a Replace function

Having a bit of difficulty tonight and could do with some pointers. My problem is surrounding the use of a table of "illegal characters" data to use within the Replace() function within a query.

I'm currently working with Replace(Replace(Replace(.etc and as I am going to need to use this on multiple queries, I don't want to have to edit each one manuallyevery time I need to add a new 'illegal character'.

I have created a table (tblBannedChars) containing the following fields: BannedChar & ReplaceChar; the general idea being that I could use Replace([string],[BannedChar],[ReplaceChar])and the outcome would be that it had repeated the Replace function for each record in the tblBannedChars table.

Find and Replace in Access 2007

I have address columns:

705 First St #5 |
804 Main St #16 |

I just want to remove the pound (#) signs. How do find/replace for just the #?

I tried the Replace Function but it deletes the entire cell. I also tried the RemoveAlpha but I get an error saying its an undefined function.

Replace Quotation Marks

So I am trying to use a query to replace quotation marks in EVERY column in a table. Now, I have two problems here.

1.) I think I am looking at replacing each column individually. For example, for a table with 4 columns, I am looking at.
Replace([Table]![Column1], etcetc), then
Replace([Table]![Column2], etcetc), then
Replace([Table]![Column3], etcetc), then
Replace([Table]![Column4], etcetc)

This is NOT a big deal to me (Problem 2 is the bigger problem!

2.) As everyone knows the Replace functions works as such so that:
Replace("alphabet", "bet", "hydro") would return "alphahydro". You are supposed to surround the 2nd function (find) and third function (replacement) with quotation marks.

However, I am trying to replace the quotation marks in the table! And in the 2nd function (find), I am surrounding the quotation mark symbol with quotation marks (so it looks like """) and it is not working!

About find/replace

I successfully used find/replace to get rid of extraneous numbers in one of my columns (alphanumeric). The results look like I123450. However all of the resulting numbers still have a 0 as the last digit and I need to get rid of them.

Does anyone know of an easy way to do this? You'd think that wildcards would work but I can't figure it out

Proper syntax for replace()

When using the replace() function, is it possible to refer to a variables in the find and replace parameters?

Replace(«string», «find», «replace»)

I can use a formula like this, where the replacement text is a table field. Replace([textin],"Find this text string",[cname])

What I want to do is get the text to find, and the variable NAME to use for replacement from a table like this:

txtfind | replacefieldname

"" [customername]
"" [shipaddress]
"" [cnumber]

Imagine a query that would replace a series of placeholders, set up as some text between brackets, with the data from a query or table that contains the three fields shown. It's like a mailmerge function.

I can't figure out the proper syntax that would refer to the txtfind and replacefieldname variables in the replace() function.

This is part of a larger project.

replace a character with a wildcard

I just split several strings into arrays, and now I need to find every "x" in the arrays and replace them with wildcards. How do I go about doing this?
Also, there isn't an "x" in every array, so it would be cool if I didn't get an error message when it can't find an "x

Replace Function

The Replace function replaces a sequence of characters in a string with another set of characters (a number of times).