eliminating certain text from a string

marej

New Member
Joined
Aug 18, 2007
Messages
15
Hi, Can anyone help? I have a list of names

The list is in either two formats as listed in column A.

I need to eliminate the brackets and the text within it, and the two letters (FU) after it.

Bloggs, Joe (Joe) Fu
Bliggs, Jon Fu

The result I want in column B is

Bloggs, Joe
Bliggs, Jon
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
marej

Welcome to the MrExcel board!

I am not sure how uniform your data is but try this:
1. Select the column by clicking its heading label.
2. Edit|Replace...|Replace what: (* | Replace with: leave blank|Replace All

If that is not what you need, post back with more details including some more sample data and expected results.
 
Upvote 0
Hi, Thank you, but that only works with the cells that have the bracket.

Bloggs, Joe (Joe) Fu Result needs to be: Bloggs, Joe
Bliggs, Jon Fu Result needs to be: Bliggs, Jon

See the second example, although there is no bracket, I still neet to get rid of the 'fu', and return Bliggs, Jon.

I have been experimenting with nesting the mid, find, and if function

This works =MID(A1,1,FIND("(",A1)-1) for the top example as it has a bracket in the text string, but does not work for the 2nd one.
 
Upvote 0
Sorry, missed that in your original post.

Still persevering with Find/Replace for the moment...
1. Do a find/replace, finding " (*)" - without the "" which were just included to show the space before the (
2. Do a second find/replace, finding " Fu"

While you are considering this, I will have a look at the formula approach.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top