Clear Chevron Characters and Characters Inside Chevrons

typewriterguy

New Member
Joined
Oct 29, 2008
Messages
24
Happy New Year, Excel folks.

Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.

Mouse, Mickey <mickey.mouse@d.com>;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

Thanks.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Happy New Year, Excel folks.

Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.

Mouse, Mickey <MICKEY.MOUSE@D.COM>;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

Thanks.
I think your example has been truncated.

Does the string contain a "less than" symbol? If so, put space characters on both sides of it. The forum software interprets the "less than" symbol as a html code character that causes text to be truncated.
 

typewriterguy

New Member
Joined
Oct 29, 2008
Messages
24
Happy New Year, Excel folks.

Is there an Excel formula to remove the spacebar + characters in red, as shown below?

I need to be left with only: last name; comma; space between comma an and first name; first name; semicolon.

Mouse, Mickey "<"mickey.mouse@d.com">"<MICKEY.MOUSE@D.COM>;

should look like Mouse, Mickey;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

Thanks.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Happy New Year, Excel folks.

Is there an Excel formula to remove the spacebar + characters in red, as shown below?

I need to be left with only: last name; comma; space between comma an and first name; first name; semicolon.

Mouse, Mickey "<"mickey.mouse@d.com">"<MICKEY.MOUSE@D.COM>;

should look like Mouse, Mickey;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

Thanks.
Assuming that the real string does not contain the quotes...

<b>Sheet2</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:284px;" /><col style="width:111px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Mouse, Mickey <mickey.mouse@d.com>;</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Mouse, Mickey;</td></tr></table> <br /><br />
=LEFT(A2,FIND("<",A2)-2)&";"
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,004
Office Version
  1. 365
  2. 2016
=left(A2, find("<", A2)-1)

copied down
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,142
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Select your cells (it is alright if you include cells without the "<..>" text in it or blank cells), press CTRL+H to bring up the Replace dialog box. Type this in the "Find what" field...

{space}"< *>"

Do NOT type space after the greater than symbol... it is there for the same reason you needed to put it there... so the symbol shows up. ALSO, do NOT type the "word" {space} as shown, rather type a single space character in for it instead (the text pattern should start with a leading space). Next, click the "Options>>" button and make sure the "Match entire cell contents" checkbox is NOT checked. Now click the "Replace All" button.... the text you want should be remaining.

NOTE: This procedure changes your original data... I wasn't sure if that is what you ultimately wanted or not.
 
Last edited:

Forum statistics

Threads
1,136,277
Messages
5,674,795
Members
419,525
Latest member
mountainkids

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
Top