628 – Text handling in Excel
Some new text-handling functions in Excel

628 – Text handling in Excel

Excel Logo

Anyone who has delved into writing formul? in Excel will probably have had to manipulate strings of text at some point, possibly to clean up formatting or to convert what Excel thinks is a simple block of text into more meaningful data that we know it to be, like a number or a date.

Pasting email addresses into Excel

There are simple ways of bulk handling text without resorting to writing a formula – copy all the names from the To: line in an Outlook email, for example: paste into a new spreadsheet and you’ll end up with a single line of text containing all of the display names and email addresses in one cell, which you may want to split up, to be of much use.

Text to Columns feature

Separate the text into multiple columns by selecting the first cell, then go to the Data tab and look for Text to Columns, which presents a fairly powerful if somewhat old-fashioned looking dialog box, to step through fixing up your text.

Paste Special, Transpose

In the example above, we have a “;” separating – or “delimiting” – each address, so we’ll use that to split the text across multiple columns.

Since we might want to create a table of names / addresses, select the cells spread across the columns, copy or cut them to the clipboard, then on a new line below, right-click and look for the Transpose option under Paste Special. Once that’s done, feel free to delete the original top row, or clear the contents of the first cell as we might come back to that row to add column names later.

Preview Text to Column

There is some other cleaning up to do with this text, though; the Text-to-Columns function chopped everything at the “;” but there’s a space which follows the semicolon, so all the Display Names after the first one have a leading space. We could repeat the Text-to-Column feature on the selection again, but use a Space as delimiter now – unfortunately that would mangle the display names into multiple columns, and if we had a smattering of users with middle names or 3 or 4 part names common in many countries, it could make things look even worse.

Custom delimiter

Using the leading “<” of the email address as the delimiter is probably simplest, as it will separate the name(s) and email addresses out, though it does still give us a few tidying-up challenges, as there are spaces we don’t want and a trailing “>” at the end of every email address.

In cases like this, it’s easier to use a formula to clean things up – the Trim function being a good place to start; it removes both trailing and leading spaces in string, so the name can be fixed up into a new column.

TRIM function
LEFT and LEN

Since we know the email address has one errant character – that trailing “>” – left behind from the earlier text-to-column operation, there are a variety of ways to strip it off. There’s the =LEFT() function, which keeps the left-most (n) characters of a string – so by combing the LEN function and knocking off a single character, we can chop the final character off.

After all this palaver, you might be thinking that some of this chopping around and formulaic string-handling can get a bit confusing as you start to nest operations within each other. Luckily, the Excel team has released some powerful new text-handling functions to try to simplify things a little:

  • TEXTBEFORE – Returns text that’s before delimiting characters 
  • TEXTAFTER – Returns text that’s after delimiting character 
  • TEXTSPLIT – Splits text into rows or columns using delimiters 
TEXTSPLIT function

So, using TEXTSPLIT on our original pasted text from the email, using “<” as the delimiter for the columns and “>; “ as the marker for the end of each row, gives us a near perfect solution – the only clanger being the trailing “>” on the last address.

You could use another formula to find and strip out any left-over characters like that, or just manually delete the last “>” off the original line you pasted in.

You decide.

要查看或添加评论,请登录

Ewan Dalton的更多文章

  • #68: It’s all about the prompt

    #68: It’s all about the prompt

    When internet search engines took off in the mid 90s – remember Alta Vista? – and Google exploded into the public…

    6 条评论
  • #67: Are you sitting comfortably?

    #67: Are you sitting comfortably?

    Regular readers of ToW might have spotted the caption under the main image of last week’s missive: it was a photo of…

    3 条评论
  • #66: A computer on every desk?

    #66: A computer on every desk?

    “A computer on every desk, and in every home, running Microsoft software” – was an early and, at the time, unbelievably…

    7 条评论
  • #65: Enshittifcation 2025 pt 1 – progressing well

    #65: Enshittifcation 2025 pt 1 – progressing well

    The “word of the year” for 2023 was “Enshittification” – as defined by author Cory Doctorow: Here is how platforms die:…

    3 条评论
  • #64: Tick, Tock, Time is up (nearly) for Windows 10

    #64: Tick, Tock, Time is up (nearly) for Windows 10

    Microsoft developed a reputation for having a couple of shaky versions of anything before the one that you’d be…

    11 条评论
  • #63B: It’s Your ISP

    #63B: It’s Your ISP

    Following on from last week’s missive on finding problems in your home network, this one turns its attention to network…

  • #63: Trouble with your network?

    #63: Trouble with your network?

    We’ve all been there. Just when you need it to work, your home internet connection goes down or huffs off in go-slow…

    12 条评论
  • #62: Will the web become exclusively mobile?

    #62: Will the web become exclusively mobile?

    Looking back over the last 50 years of technology progress, the internet must surely be the most significant change…

    2 条评论
  • #61: Adios, Office!

    #61: Adios, Office!

    Microsoft is seemingly ditching it’s “Office” brand, which first appeared in 1990 to describe the now-familiar bundling…

    12 条评论
  • #60: The problem with coupons

    #60: The problem with coupons

    Lots of online shops have promo codes that can be entered as part of the checkout process, to get a discount, free…

社区洞察

其他会员也浏览了