how to change some text with certain letter string, but not all text that contains string

[waiting4utopia]

Need to change "Sch" to "School" in many cells but do not want to replace "Schwartz"(or any other word that contains Sch in it) with "School". I have tried substitute and replace functions, but it even changes "Schwartz". Thanks for help! I'm spending way too much time on this! Darn It!

[JieJenn]

Under Replace, check Match entire cell contents to replace your texts.
4-24-2017 2-47-19 PM.jpg

[waiting4utopia]

I've done this and get error: we couldn't find what you were looking for. click options for more ways to search

how to I put a screen shot on here?

[waiting4utopia]

I keep getting error "couldn't find what your looking for"

Having trouble posting screen shot

[JieJenn]

Can you post a sample file and as well as results you expected.

[waiting4utopia]

What it is now: What I need:
Schafer Park Elementary Schafer Park Elementary
McNeel Sch - Vacca Campus McNeel School - Vacca Campus
Sequoyah Sch - Chalkville Campus Sequoyah School - Chalkville Campus

In one column, I have a list of schools, I need the abbreviation "Sch" changed to "School" but NOT if "sch" is within a name. I have over 100,000 to sort through so I need to know the formula for many cells.

[FlameRetired]

waiting4utopia welcome to the forum.

Try it this way.

• Select all the data relevant in column A.
• Bring up Find and Replace.
• In the Find what text box type Sch and be sure to include a trailing space ... Ex. "Sch " ... without the quotes.
• In the Replace with what text box type School and be sure to also include a trailing space.
• Click Replace All.

If this still doesn't work try uploading a small sample Excel workbook.

If you are not familiar with how to do this:

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

[waiting4utopia]

I attempted to attach workbook. I've tried the space after sch and school but it didn't work. Thanks for your help.

[FlameRetired]

I attempted to attach workbook. I've tried the space after sch and school but it didn't work. Thanks for your help.

That's because the data in the upload isn't like in the screen shot. Each is at the end of the string.

If the upload isn't a comprehensive representation of what you are working with we're likely to have a high fail rate on any solutions.

In the meantime try this formula in B2 and fill down.

Please Login or Register  to view this content.

	A	B
1	Needs Changed
2	Northwest Prep Charter	Northwest Prep Charter
3	Jack London Elementary	Jack London Elementary
4	Morrice Schaefer Charter	Morrice Schaefer Charter
5	Schallenberger Elementary	Schallenberger Elementary
6	William R Martin Middle Sch	William R Martin Middle School
7	Valley Grande Elem Sch	Valley Grande Elem School
8	Tipton Middle Sch	Tipton Middle School
9	Southside Primary Sch	Southside Primary School
10	Southside High Sch	Southside High School
11	JE Terry Elem Sch	JE Terry Elem School
12	Dallas Co High Sch	Dallas Co High School
13	Brantley Elem Sch	Brantley Elem School
14	Keith Middle-High Sch	Keith Middle-High School
15	Bruce K Craig Elem Sch	Bruce K Craig Elem School
16	Salem Elem Sch	Salem Elem School

[FlameRetired]

Another possibility could be.

Please Login or Register  to view this content.

[waiting4utopia]

I attempted to give an adequate representation, but I have a large amount of data, so I'm doing the best that I can not knowing exactly what you require.

The formula you gave me works for data if "sch" is at the end of the string.

But I have data that has "sch" within the string that needs changed, i.e.:

McNeel Sch - Vacca Campus - Needs changed to McNeel School - Vacca Campus
Schwartz Elementary - Don't want to change: Schwartz Elementary

[Phuocam]

Or try this ...

=TRIM(SUBSTITUTE(A2&" ","Sch ","School "))

[FlameRetired]

I copied and pasted the examples from post #11 and did the "Sch " "School " find and replace. It worked at my end.

Is it possible there is no space in your real data? I.e. Sch is followed immediately by -? I couldn't tell from the screen shot.

Try find and replace with "Sch-" > "School-".

If you upload again please include some examples of Sch in the middle of the string as in the screen shots.

[waiting4utopia]

Looks like this one worked! Wow, super complicated! Thank you for your time

[waiting4utopia]

I should say that Flameretired's second formula worked! Wow! complicated!

[FlameRetired]

You're welcome. Glad to hear it works,and thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.