Replacing Wildcard Text in Find/Replace

[Xveckthorn]

Is there away to “move” the info that’s captured with the * wildcard character when you’re using Find & Replace?



For example:



I’m looking at a sheet with the following in Column A

1 Public Records of Baltimore County, State of Maryland

2 Public Records of Cuyahoga County, State of Ohio

3 Public Records of Allegheny County, State of Pennsylvania

4 Public Records of St. Louis County, State of Missouri



If I wanted to change that to say:

1 Baltimore County, Maryland

2 Cuyahoga County, Ohio

3 Allegheny County, Pennsylvania

4 St. Louis County, Missouri


I can use Find & Replace to isolate and “cut out” the name of the County, but is there away to replace the name of the county (or, more broadly, any text that’s captured by the wildcard *) in the “Replace” portion of Find & Replace?



For example, let’s say adding a * in the Find field and a & in the Replace field will take whatever text is captured by the *, and adds it back in the Replace field where you have the &. In that example, I could do:



Find: Public Records of * County, State of

Replace: & County,



IS THIS POSSIBLE!? Been trying to figure this out for over a year now – it would make my job SO much easier – ANY help would be appreciated.

 

[Joe4]

Welcome to the Board!

If it is really as simple as the example you have shown, I would do it in the following two steps:
1. Replace "Public Records of " with nothing
2. Replace "State of " with nothing

Would that work for you?

 

[Xveckthorn]

What I haveup there is really just an example that I was trying to use to convey thequestion of

Is there a wayto replace the text captured with the * wildcard in the Find & Replacefeature in the Replace field?

Where I work,I deal with properties in multiple counties in multiple states. Each state hasits own way of describing their public records. We have a typing team thatalways types “{County} County, {State} Records”. I’m trying to find a way ofkeeping the name of the County as they’ve typed it, but replacing all of theother text. For example, I’m trying to replace:

St. LouisCounty, Missouri Records
Cass County,Missouri Records
JacksonCounty, Missouri Records
Clay County,Missouri Records

With:

records ofSt. Louis County, Missouri
records ofCass County, Missouri
records ofJackson County, Missouri
records ofClay County, Missouri

By doing aFind & Replace, and using * to capture the name of the County, andreplacing it with the string of text of: records of {Text Captured with *},Missouri

 

[Joe4]

I am going to venture to say that you are probably going to need to create a VBA procedure to do this.
The problem is that "*" will capture everything, not just the word you are looking for. "*" doesn't know the difference between a city or state name from any other text.

Each state has its own way of describing their public records.

This could make things very difficult and complicated, if you are dealing with many different formats.
Your first step would be to identity all the different data formats you get, and what each one should be changed to.

If you can provide examples of all the different possible formats, then we might be able to figure out a way to something with that (provided there really aren't 50 different formats).
Also, if each states data is coming from different sources/files, you may be able to handle each one as it is being imported, instead of trying to correct all the states data at the same time.

 

[Xveckthorn]

The only formatthat I need to change is:

“{County}County, {State} Records”

I know how todo it with filters and by doing each county separately by inputting the county’sname instead of a *. What I’m trying to figure out is if I can do ALL of thecounties at once by just moving their placement in a string of text. All of thetext to be changed is in the same column.

 

[Joe4]

Now I am getting confused.

In your first post, it looks like you want to change formats:
FROM: Public Records of Baltimore County, State of Maryland
TO: Baltimore County, Maryland

In your second post, you said that each state has their own way of doing (suggesting multiple formats), as said the format change is:
FROM: St. Louis County, Missouri Records
TO: records of St. Louis County, Missouri

In your last post, you say that the only format you need to change is:
{County} County, {State} Records
(I have no idea if that is the format you need to change FROM, or the format you need to change TO).

I already mentioned how you can address the situation you described in your first post. You could also use a formula option like this, for an entry in cell A1:

=SUBSTITUTE(SUBSTITUTE(A1,"Public Records of ",""),"State of ","")

For the situation described in your second, you could use this formula:

="records of " & SUBSTITUTE(A1," Records","")

I am not sure what you are saying in your last post. If it is anything different than the first two posts, please clarify. The FROM/TO methodology like I showed above should be sufficient.

 

[Xveckthorn]

My friend, SUBSTITUTE... is the answer that I have been looking for. THANK YOU!!!!!!!!!!

 

[Joe4]

You are welcome.