Divide sticky text into parts

So, we have a column with data that needs to be divided into several separate columns. The most common life examples:

  • Full name in one column (and it is necessary – in three separate ones, so that it is more convenient to sort and filter) 
  • a full description of the product in one column (but you need a separate column for the manufacturer, a separate one for the model for building, for example, a pivot table)
  • the entire address in one column (and it is necessary – separately the index, separately – the city, separately – the street and the house)
  • etc.

Go..

Method 1. Text by columns

Select the cells to be split and select from the menu Data – Text by Columns (Data — Text to columns).A window will appear Text parser:

Divide sticky text into parts

On the first step Masters choose the format of our text. Or is it text in which some character separates the contents of our future separate columns from each other (with separators) or in the text, using spaces, columns of the same width are imitated (fixed width).

On the second step Masters, if we chose the format with delimiters (as in our example) – you need to specify which character is the delimiter:

Divide sticky text into parts

If the text contains lines where for some reason there are several separators in a row (several spaces, for example), then the checkbox Treat consecutive delimiters as one (Treat consecutive delimiters as one) will make Excel treat them as one.

Drop-down list Row Delimiter (Text Qualifier) it is necessary that the text enclosed in quotation marks (for example, the name of the company “Ivanov, Mann and Farber”) is not divided by comma

inside the title.

And, finally, at the third step, for each of the resulting columns, selecting them in advance in the Wizard window, you need to select the format:

  • general – leave the data as is – suitable in most cases
  • date – must be selected for columns with dates, and the date format (day-month-year, month-day-year, etc.) is specified in the drop-down list
  • textual – this format is needed, by and large, not for columns with full name, city or company name, but for columns with numeric data, which Excel must necessarily perceive as text. For example, for a column with customer bank account numbers, where otherwise it will be rounded to 15 characters, because. Excel will treat the account number as a number:

Divide sticky text into parts

Button Details (Advanced) allows you to help Excel correctly recognize separator characters in text if they differ from the standard ones specified in the regional settings.

Method 2. How to pull out individual words from the text

If you want this division to be done automatically without user intervention, you will have to use a small VBA function inserted in the book. To do this, open the Visual Basic editor:

  • in Excel 2003 and older – menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor)
  • in Excel 2007 and later, tab Developer – Visual Basic Editor (Developer — Visual Basic Editor) or keyboard shortcut Alt + F11

Insert a new module (menu Insert – Module) and copy the text of this custom function there:

Function Substring(Txt, Delimiter, n) As String  Dim x As Variant      x = Split(Txt, Delimiter)      If n > 0 And n - 1 <= UBound(x) Then          Substring = x(n - 1)      Else          Substring = ""      End If  End Function

Now you can find it in the list of functions in the category User Defined (User Defined) and use with the following syntax:

=SUBSTRING(Txt; Delimeter; n)

where

  • Txt - cell address with text to be divided
  • Delimeter - separator character (space, comma, etc.)
  • n - sequence number of the fragment to be extracted

For example:

Divide sticky text into parts

Method 3. Separating sticky text without spaces

It's a tough one, but it happens. We have a text without spaces at all, sticking together into one long phrase (for example, full name "IvanovIvanIvanovich"), which must be separated by spaces into separate words. A small macro function can help here, which will automatically add a space before capital letters. Open the Visual Basic editor as in the previous method, insert a new module there and copy the code for this function into it:

Function CutWords(Txt As Range) As String      Dim Out$      If Len(Txt) = 0 Then Exit Function      Out = Mid(Txt, 1, 1)            For i = 2 To Len(Txt)          If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then              Out = Out & Mid(Txt, i, 1) & " "          Else              Out = Out & Mid(Txt, i, 1)          End If      Next i      CutWords = Out  End Function

Now you can use this function on the sheet and bring the sticky text back to normal:

Divide sticky text into parts

 

  • Dividing text with the out-of-the-box PLEX add-on feature
  • What are macros, where to insert macro code, how to use them

 

Leave a Reply