0

I have a problem with new Outlook (Office 365), where they "improved user experience" by removing the < and > characters which I used before for separating mail address from the user name.

I copied all addresses from Outlook, pasted in the body of mail, clicked replace the ; < by a paragraph mark, this gave me a column which I copied into Excel column A. I had a formula =IFERROR(MID($A2;FIND("<";$A2)+1;LEN(A2)-FIND("<";$A2));A2) in B column, and the result, i.e. clear mail address only, I could check against our internal database for various purposes.

Now I am stuck without these parentheses - I can target the search/find formula to find a full stop . character; I also know that the address starts at the first space to the left from it, yet with the insane diversity of our staff it could be the second, third or fourth space from the left.

The sample address could be e.g. Woodward, Robert Paul robert.woodward@ourcompany.com, or Myname, Petr petr.myname@ourcompany.com, or Xyz, Roxana Denise Ariela roxana.xyz@ourcomany.com. I am not sure if I should try to change the Outlook settings somehow so it starts showing the brackets again (tried but failed to find), or if there is a formula looking for first space to the left from .. =find() formula looks from left to right (unless I change my writing direction), so after many years of experience, I am clueless, yet hoping I am not alone with the same issue.

Robin Sage
  • 969
  • 1
  • 8
  • 24
Petr Hrubý
  • 3
  • 3

3 Answers3

2

As an alternative you can use below formula-

=TRIM(RIGHT(SUBSTITUTE(A1," ", REPT(" ",200)),200))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    Nice solution Harun – JvdV Aug 31 '21 at 09:21
  • @JvdV Many of beginner (moderate users also) feel uncomfortable with `FILTEXML()` formula. So, this will help then. – Harun24hr Aug 31 '21 at 09:24
  • 1
    Moreover, it will be possible to use this sollution on Mac and Excel Online. – JvdV Aug 31 '21 at 09:25
  • Wow I agree it is difficult to grasp the FILTEXML() solution, but this solution blows me away as well. And I am rather considered a formula guru. :-D – Petr Hrubý Sep 01 '21 at 13:29
  • In this particular case Substitute & Rept is fine but FILTERXML() is giant formula and have too many complex use case to solve. – Harun24hr Sep 01 '21 at 13:36
0

A very general idea is to "split" a string on a specific character (space in your case). You can use:

enter image description here

Formula in B1:

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[last()]")
  • "<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>" - This creates a valid XML-string.
  • With the help of FILTERXML() we can analyse this XML-string with given valid XPATH expression: "//s[last()]", meaning: return the very last node.

For more information on FILTERXML() and it's use to "split" a string on any given character, please refer to this older post.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Thank you - I couldn't add thumb up as I am registered relatively recently (though using stackoverflow since maybe 1989 :), but yes I would call this elegant, I used it in my table. – Petr Hrubý Aug 31 '21 at 14:09
0

Well, I resolved it for the time being, albeit clumsily, by placing this formula into the B column.

"=MID(A2;FIND(" ";A2)+1;LEN(A2)-FIND(" ";A2)+1)" (the semicolons need to be replaced by commas for EN setup), and copying the same formula across to C, D and E column, until I got all results in the entire column spaceless (I have 150 rows in there).

As this creates the "#VALUE!" error when there are no more spaces to find, I placed the error handling in there: "=IFERROR(IFERROR(MID(D2;FIND(" ";D2)+1;LEN(D2)-FIND(" ";D2)+1);D2);C2)", which redirects the formula to the next left non-error result. (I could even replace the D2 and C2 by the actual D2 and C2 formulas and it would be possible to have it all just in one column, but considering the fact that the text formulas are usually done for quick need and should be faster to create than manual processing of individual records one by one, it is nothing I could call "elegant").

I could consider perhaps creating an UDF function that would be checking for spaces in the right to left direction, but it is not worth it.

JW Geertsma
  • 857
  • 3
  • 13
  • 19
Petr Hrubý
  • 3
  • 3