Excel split string - IT Справочник
Llscompany.ru

IT Справочник
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel split string

Split text into different columns with functions

You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data. For example, you can distribute the first, middle, and last names from a single cell into three separate columns.

The key to distributing name components with text functions is the position of each character within a text string. The positions of the spaces within the text string are also important because they indicate the beginning or end of name components in a string.

For example, in a cell that contains only a first and last name, the last name begins after the first instance of a space. Some names in your list may contain a middle name, in which case, the last name begins after the second instance of a space.

This article shows you how to extract various components from a variety of name formats using these handy functions. You can also split text into different columns with the Convert Text to Columns Wizard

One middle initial

Two middle initials

Last name first, with comma

Two-part first name

Three-part last name

Barreto de Mattos

Two-part last name

Last name and suffix first, with comma

Hyphenated last name

Note: In the graphics in the following examples, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

This example separates two components: first name and last name. A single space separates the two names.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Result (first name)

‘=LEFT(A2, SEARCH(» «,A2,1))

=LEFT(A2, SEARCH(» «,A2,1))

Result (last name)

The first name starts with the first character in the string (J) and ends at the fifth character (the space). The formula returns five characters in cell A2, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left.

The last name starts at the space, five characters from the right, and ends at the last character on the right (h). The formula extracts five characters in A2, starting from the right.

Use the SEARCH and LEN functions to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)

Count the total length of the text string, and then subtract the number of characters to the left of the first space, as found in step 1.

This example uses a first name, middle initial, and last name. A space separates each name component.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

One middle initial

Result (first name)

‘=LEFT(A2, SEARCH(» «,A2,1))

=LEFT(A2, SEARCH(» «,A2,1))

Result (middle initial)

‘=MID(A2,SEARCH(» «,A2,1)+1,SEARCH(» «,A2,SEARCH(» «,A2,1)+1)-SEARCH(» «,A2,1))

=MID(A2,SEARCH(» «,A2,1)+1,SEARCH(» «,A2,SEARCH(» «,A2,1)+1)-SEARCH(» «,A2,1))

Live Result (last name)

‘=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,SEARCH(» «,A2,1)+1))

=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,SEARCH(» «,A2,1)+1))

The first name starts with the first character from the left (E) and ends at the fifth character (the first space). The formula extracts the first five characters in A2, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)

The middle name starts at the sixth character position (S), and ends at the eighth position (the second space). This formula involves nesting SEARCH functions to find the second instance of a space.

The formula extracts three characters, starting from the sixth position.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5).

Add 1 to get the position of the character after the first space (S). This numeric position is the starting position of the middle name. (5 + 1 = 6)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 4. This character number is the ending position of the middle name. (8)

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the sixth position found in step 2. (8 – 5 = 3)

The last name starts six characters from the right (K) and ends at the first character from the right (n). This formula involves nesting SEARCH functions to find the second and third instances of a space (which are at the fifth and eighth positions from the left).

The formula extracts six characters in A2, starting from the right.

Use the LEN and nested SEARCH functions to find the value for num_chars:

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 2. This character number is the ending position of the middle name. (8)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second instance of space found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 – 8 = 6).

Читать еще:  База данных в excel vba

Here’s an example of how to extract two middle initials. The first and third instances of space separate the name components.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

VBA Split Function

What is VBA Split Function in Excel?

Split function in VBA is a very useful string function which is used to split strings into multiple substrings based on a delimiter provided to the function and a comparison method, there are other string functions too which converts a string into substring but split function can split a string into more than one substrings.

In normal worksheet functions LEFT, RIGHT, and MID functions are used as text functions to extract the portion of the sentence. For example extraction of first name, middle name, and the last name is the common scenarios we have seen. But in VBA we have the more versatile function called SPLIT which will do a similar job for you. SPLIT is a built-in function in Excel VBA which can split the supplied sentence based on the delimiter. For example, if you want to split the email address to different parts the common element in the email address is “@” in all the email id’s, so “@” becomes delimiter here.

VBA Split String Function

Like all the other functions split too has its own syntax. Below are the parameters of the excel VBA Split string function.

  • Value or Expression: This nothing but the actual value we trying to split. For example, if you want to split first name and last name, the full name is the value here.
  • [Delimiter]: What is the common element to split the Value or Expression? In email Id’s “@” is the common element, in address comma (,) is the common element. If you ignore this it considers the space character as the default value.
  • [Limit]: How many substrings you want from the Value or Expression you have supplied. For example, if the value is “My name is Excel”, if you supply 3 as the limit it will show the result in three lines like “My”, “name”, “is Excel”.
  • [Compare]: Since we don’t use compare argument skip this optional argument.

In the next sections of the article, we will see how to use the SPLIT function in excel VBA practically.

Examples of VBA Split String Function

Below are the practical examples of the Split Function in Excel VBA.

Example #1 – Split the Sentence

Split returns the result in the array which will start from 0. All the arrays are starts from 0 not from 1.

Assume you have the word “My Name is Excel VBA” in cell A1.

Now you want to split this sentence into pieces like “My”, “Name”, “is”, “Excel”, “VBA”. Using Excel VBA SPLIT String function we can return this result.

Step 1: Start the macro with the name.

Code:

Code:

Step 3: Now for the defined variable, My Text assigns the word “My Name is Excel VBA”.

Code:

Step 4: Now for My Result variable apply VBA split string function.

Code:

Step 5: Expression is our text value. Since we have already assigned our text value to variable My Text enter this argument here.

Code:

Note: As on now ignore all the other parameters.

Step 6: So now My Result holds this split result. As I told earlier in the post, split function stores the result as an array so here

  • My Result (0) = “My”
  • My Result (1) = “Name”
  • My Result (2) = “is”
  • My Result (3) = “Excel”
  • My Result (4) = “VBA”

Even though this code does not make any impact to start off the SPLIT function we can use this code.

Example #2 – VBA SPLIT String with UBOUND Function

In order to store the result of the SPLIT function, we can use UBOUND function along with SPLIT function.

UBOUND will returns the maximum length of the array. In the above example, the maximum length of the array was 5.

Take the same word “My Name is Excel VBA”. Let’s split this word and store from cell A1 onwards.

Step 1: Let’s continue from where we left off in the previous example.

Step 2: Now apply FOR NEXT LOOP in VBA from 0 to maximum length of the array i.e. UBOUND.

The reason we started from zero because SPLIT will store the result from zero, not from 1.

Step 3: Now apply VBA CELLS property and store the result.

Code:

Step 4: Run this code, we would have split values.

Complete Code:

Return Word Count

We can also show the total number of words in the supplied value. Use the below code to show the total number of word counts. Code:

Copy and paste the above VBA code and run it, the message box will return the result.

Things to Remember

  • If the delimiter is not supplied SPLIT automatically thinks the delimiter as space.
  • If you want to split except space then you need to specify the delimiter in double quotes.
  • SPLIT stores the result as array results.
  • UBOUND function returns the maximum length of the array.

Recommended Articles

This has been a guide to VBA Split Function. Here we learned how to use VBA Split String Function in Excel along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

VBA Split Function – How to Use

VBA, just like any other language gives you a set of functions to perform various operations on strings. And VBA Split is one of those string functions. Microsoft introduced Split function with VBA Version 6 (in Office 2000).

As the name suggests, the job of Split statement is to break, split or divide a string based on a particular criteria.

Split is one of the many Substring functions that can be used in VBA. I have written a whole post on these Substring functions, you can read it here.

Definition and Syntax of VBA Split Function:

Split can be defined as a function that can split a text string into an array, by making use of a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string.

The Syntax of VBA Split Statement is as follows:

Читать еще:  Панель инструментов стандартная excel

Split ( text_string , delimiter , limit , compare )

Here, ‘ text_string ’ refers to the string that you need to break.

‘ delimiter ’ refers to the character which divides the string into parts. This is an optional argument, space character “ ” is treated as the default delimiter.

‘ limit ’ is an optional parameter. It specifies the maximum number of parts into which the input string should be divided. The default value is -1, which means that the string should be divided at each occurrence of ‘ delimiter ’.

‘ compare ’ is also an optional argument that specifies the comparison method to be used while evaluating the strings. ‘ compare ’ argument can have anyone of the below values:

Some Important points about Split Function:

  • Split Function returns a String Array and not a String.
  • If you omit the ‘compare’ argument then, Split Statement uses the Binary comparison method as default.
  • If the ‘text_string’ that you pass to Split is a zero length string then, it returns a single-element array containing a zero-length string.
  • If the ‘delimiter’ is not found anywhere in the ‘text_string’, then Split returns the single-element array containing ‘text_string’ as it is.

How to Use VBA Split Function:

Ok, now let’s see how you can use the Split function:

Objective: Let’s consider we have a string : “How are you” and now our objective is to break this string to separate out the words.

So, we will try to apply a Split Statement to it as:

Split ( text_string , delimiter , limit , compare )

‘ text_string ’: In this case our ‘text_string’ would be “How are you”.

‘ delimiter ’: In this case our delimiter would be space character (“ ”). But as I have foretold that, space character is the default delimiter, so will leave this argument blank.

‘ limit ’: We will also leave the ‘limit’ argument blank because we need to separate out all the words from the given ‘text_string’.

‘ compare ’: This would be blank, as blank specifies binary comparison method.

So, the final Split Function would be:

Split(«Hello how are you»)

Note: Now we need to receive the return from this split function, for this purpose we can use a string array.

So, the final code would be:

5 Examples using Split Function:

Now let’s move on to some examples of Split Statements:

Example 1: Separate a list of pipe separated values i.e. A|B|C|D.

Below is the code to do this:

Example 2: Break the string (say: “Welcome to Excel Trick”) into separate words by using Split Function. And display the contents of the array returned by the Split using a message box.

Below is the code that can accomplish this task:

In this code, we have used a Split function to break the string into parts. Later, in the code we have used a For loop to iterate the array returned by the Split Function in order to show the array contents using a message box.

Example 3: Use the above example, but here after splitting the string we just need to display its third part. To make it clearer, let’s say if I divide the string (“Welcome to Excel Trick”), then in this case I just need the output as “Excel” i.e. the third word in the string.

So, the code would be:

In this code, you would have noticed that instead of using a String Array for accepting the return from the Split Function I have taken a string variable. This is because by writing the Split Statement as: WrdString = Split(text_string)(2) , we are telling it to return only the third element of the array (which is a string) and not the complete array.

Example 4: Now use the same code of Example 2 just change it to demonstrate the use of limit argument.

Above code is very similar to the one that we have seen in Example 2. The only difference here is that we have supplied the limit argument to Split function.

Notice: Because of the limit argument, Split function divides the text string into 3 parts instead of 4.

Example 5: Write a code using Split function that can count the number of words in a string.

Below is the code that can do this:

In this example, we have used the UBound function to get the upper bound of WrdArray() . The statement UBound(WrdArray()) gives the length of WrdArray() . But as we all know, that an array starts from index 0, so we need to add 1 to its upper bound to get the actual word count.

Example 6: Write a code that can import a CSV to a spreadsheet.

This code requires you to understand the basics of FileSystemObject. So, before moving ahead I would recommend you to read this post .

Below is the code to do this:


Explanation:

As we know that CSV files are comma separated files. In this example, we are reading each line from a pre-existing CSV file and then by using split function we are separating individual values from each line and writing them to the Active Sheet.

So, this was all about VBA Split Function from my side. Feel free to drop in your comments related to the topic.

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into ‘Excel Geeks’.

The Excel VBA Split Function

In a previous section on strings, we mentioned that there is an inbuilt function called Split. We said that this comes in handy if you want to split a name that has more than two part. Our previous code only worked for people who had one first name and a surname. But what if the name you come across in cell A1 is something like David Lloyd George? Well, that’s where the Split function can help.

The Split function looks like this:

Between the round brackets of Split you need two things. The first is the string you want to break up into separate pieces. After a comma, you then need something called the separator. This is whatever character is between each piece of text you want to split. It could be a blank space, a comma, a dash, just about anything.

(NOTE: there are also two optional parameters you can add to Split, a limit and a compare method. The limit is an integer and is used to restrict the number of pieces that Split produces. The compare methods are CompareMethod.Binary and CompareMethod.Text.)

As an example of Split, enter the name David Lloyd George into cell A1 on your spreadshet. Now try out the following code:

Читать еще:  C и excel

The first two lines set up a String and an Integer variable, txt and i. The third line sets up a variable of type Variant. A Variant type is needed if you’re going to be using Split. Any other variable type won’t work.

The fourth line just gets the value of the ActiveCell on the spreadsheet and places its contents into the txt variable. Next comes the Split line:

To the left of the equal sign we have our FullName Variant variable. The pieces of the string will be split and stored here, turning FullName into an array.

To the right of the equal sign we have the Split function:

The first item between the round brackets of Split is the text we want to split. The text for us is held in the variable called txt. But you don’t have to store your text in variable. You could just have direct text surrounded by double quotes:

Split(«David Lloyd George», » «)

The second item between the round brackets of Split is the separator. We want to separate each part of the string wherever spaces are found. We’ve typed two double quotes. Between the double quotes, we tapped the space bar on the keyboard once. The Split function will then search for 1 space between each word of the text, and break it into chunks where this space is found. Each chunk will be one item in the array.

To get at each piece of the new FullName array, we have a For loop:

For i = 0 To UBound(FullName)

The loop goes from 0 (the first position in the array) to the highest position in the array. We get this highest position by using UBound.

As the code for the loop, we have this:

Cells(1, i + 1).Value = FullName(i)

Here, we’re using Cells to access the first Row, which is the hard-coded 1. To move across the columns we have i + 1. To the right of the equal sign we have our new array, FullName. To get at each position in the array we have a pair of round brackets after the array name. Between the round brackets we have the loop variable, i. This will move us through the array, as i increases by 1 each time round.

When you enter a name into cell A1 of your spreadsheet, it will look something like this:

Return to the VBA editor and run your code. Your spreadsheet should change to this:

So we’ve taken a name from one cell and split it over three cells.

Delete the names in all the cells of your spreadsheet. Now enter an even longer name, something like «John Allen Joe Jones». Run your code again and you’ll find that all four parts end up in different cells.

The Join Function

You can put the pieces of an array back together again with the Join function. If you want, you can have the same separator as before, but you can also have a new one. In the code below, we first have a name separated by spaces. We then use Join to put the name back together again, but this time separated by hyphens.

Dim txt As String
Dim FullNameSpaces As Variant
Dim FullNameHyphens As Variant

txt = «David LLoyd George»

The Join line is this:

In between the round brackets of Join, you first need the name of the array you’re puting back together as one piece of text. After a comma, you type the new separator, surrounded by double quotes.

In the next section of this Excel VBA course, we’re going to take a closer look at Subs and Function.

Excel VBA-использование функции Split, но с несколькими / переменными строками

У меня есть столбец с десятками / сотнями разных имен, некоторые перечислены несколько раз. Каждое имя имеет формат firstname.lastname. Мне нужно split эти До правильно перечислены как firstname фамилия. Используя функцию split, обычно первым параметром является строка, необходимая для split.

Я использовал это раньше с массивом элементов до split, но мне всегда приходилось перечислять каждый элемент в массиве. Это, конечно, невозможно с сотнями разных имен.

Я искал, но каждый ответ, который я нахожу, всегда использует массив или обсуждает, как разбить split на несколько столбцов.

Как это можно написать так, чтобы код мог проходить через весь столбец (B, в данном случае) и split каждое имя, сохраняя все в одном столбце?

Код будет что-то вроде:

2 Ответа

Выберите столбец, нажмите control и F, чтобы открыть меню найти и заменить, найти «.»и заменить введите пробел (нажав пробел один раз). Предполагается, что у вас нет другого текста в ячейках, содержащих точки.

Вы могли бы сделать то же самое в VBA.

Код для этого выглядит так

Похожие вопросы:

У меня есть функция с несколькими переменными, и я хотел бы использовать с ней функцию map(). Пример: def f1(a, b, c): return a+b+c map(f1, [[1,2,3],[4,5,6],[7,8,9]])

Я использую VBA, но не на excel. Я знаю, что на VBA для Excel вы можете сделать что-то вроде Split(String will be splitted) и получить массив обратно. Есть ли способ выполнить split без этой.

Пожалуйста, имейте в виду, что я работаю с серией медицинских информационных баз данных

1000 line. Из-за размера баз данных ручная обработка данных занимает слишком много времени. Поэтому я.

Я хотел бы использовать переменные в функции диапазона, чтобы назначить адаптивный выбор диапазона в excel VBA. Range(A&aa+4:C5&bb+4).Select где aa и bb-переменные. Спасибо!

В Excel (2016) у меня есть некоторые данные, которые выглядят следующим образом: Основываясь на конкретном столбце, В данном примере столбец ‘IPAddress’, если в ячейке имеется несколько строк.

Я хочу разделить строку с несколькими разделителями, используя Excel VBA. Одна из струн-это: d1-d2 d3 d4 У нас есть тире и пробел в качестве двух разделителей. Я попробовал функцию split , но она.

У меня есть приведенная ниже формула, отлично работающая над большими диапазонами данных в Excel 2007. Во всех случаях Range1, Range2 и ArrayRange являются одинаковыми начальными и конечными.

У меня есть один лист с кодом VBA со многими переменными и константами, и я хотел бы использовать эти переменные даже в коде VBA другого листа. К сожалению, код разделен между листами и требует.

Я хочу, чтобы пользователь моего файла excel ввел предложение в ячейку B2, а затем имел суб для разбора предложения в другом столбце (от D2 до Dn). Так, например, если вы наберете aaa bbb ccc ddd в.

Я унаследовал таблицу Excel со многими функциями VBA, и неясно, где (или даже если) многие из них используются. Есть ли способ найти использование функции VBA или, возможно, поиск ее вызовов.

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector