Replacing Wildcard Text in Find/Replace

Xveckthorn

New Member
Joined
Nov 13, 2018
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 13, 2018
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 13, 2018
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
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:
Code:
=SUBSTITUTE(SUBSTITUTE(A1,"Public Records of ",""),"State of ","")


For the situation described in your second, you could use this formula:
Code:
="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

New Member
Joined
Nov 13, 2018
Messages
4
My friend, SUBSTITUTE... is the answer that I have been looking for. THANK YOU!!!!!!!!!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top