Trim off (A) only , leave rest of cell .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
701
Office Version
  1. 2016
Platform
  1. Windows
Hello all , i was wanting too get a function which trims off the (A) part of col A but leaves the entire rest of the cell . Names can vary in lenght . Have tried standard trim but of course ends up trimming last few letters of each name .

Answers required are in col B .

Thanks .
Excel Workbook
AB
1JockeyAnswer
2Ryan BishopRyan Bishop
3KC Walters (A)KC Walters
4Maija Vance (A)Maija Vance
5Jonathan RiddellJonathan Riddell
6Vinnie ColganVinnie Colgan
7Sam SprattSam Spratt
8Lisa AllpressLisa Allpress
9Jonathan RiddellJonathan Riddell
10Rory Hutchings (A)Rory Hutchings
11Lisa AllpressLisa Allpress
12Matthew CameronMatthew Cameron
13Lynsey SatherleyLynsey Satherley
14Shankar Muniandy (A)Shankar Muniandy
15Lee CallawayLee Callaway
16Jillian MorrisJillian Morris
17Ashley McKay (A)Ashley McKay
18David WalshDavid Walsh
19James McDonald (A)James McDonald
20Matthew CameronMatthew Cameron
21Lisa AllpressLisa Allpress
22James McDonald (A)James McDonald
23Trudy ThorntonTrudy Thornton
24Mark HillsMark Hills
25Mereana Hudson (A)Mereana Hudson
26Hayden TinsleyHayden Tinsley
27David WalkerDavid Walker
Sheet1
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try following (Cell B2):
=IF(ISNUMBER(FIND("(A)",A2,1)),LEFT(A2,FIND("(A)",A2,1)-2),A2)
Copied down
 
Upvote 0
Do it in the original column?

1. Select the whole column by clicking its heading label

2. Use Find/Replace with
Find what: " (A)" (without the "" - I just included them to show the space before the opening bracket)
Replace with: leave blank
Check in Options>> that 'Match entire cell contents' is not selected
Replace All
 
Upvote 0
Try following (Cell B2):
=IF(ISNUMBER(FIND("(A)",A2,1)),LEFT(A2,FIND("(A)",A2,1)-2),A2)
Copied down
Shrivallabha

A trick if you want to extract the left part like that is to add the text you are looking for on the each cell value (in the formula) then you don't need to test whether it is found or not, because it always will be. Like this:

=LEFT(A2,FIND(" (A)",A2&" (A)")-1)
 
Upvote 0
Thanks to all for replies .

Peter_SSs and mikerickson , both work exactly , thankyou .
 
Upvote 0
I'm not sure which of my posts you are referring to but my formula post was not really a suggestion to you, since Mike had a pretty simple formula already, but a suggestion for Shrivallabha to consider for the future.

If you are using Mike's formula, it may not matter to you unless you are using these names in a lookup or comparing to other data, but I think Mike accidentally mixed up where he put the space in his formula. The result is that all those apprentice names will actually have two spaces on the end of them.

I think the intention would have been

=SUBSTITUTE(A2," (A)","")
 
Upvote 0
Shrivallabha

A trick if you want to extract the left part like that is to add the text you are looking for on the each cell value (in the formula) then you don't need to test whether it is found or not, because it always will be. Like this:

=LEFT(A2,FIND(" (A)",A2&" (A)")-1)

Thank you, Peter. I really appreciate the help and insight I gain on this forum.
 
Upvote 0

Forum statistics

Threads
1,223,521
Messages
6,172,813
Members
452,481
Latest member
Najwan

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