It is better to copy the source data in new sheet. If you have data in columns to the right of source text, they may get replaced. Let's see how you can use it split text based on a specific character.įirst thing, have enough empty columns so the right of the source text. Using this feature you can split original text into multiple columns, on each appearance of the separator. There's a built in feature to split text into columns. How do we split text on each appearance of a specific character? We will have to use more formulas and that would be tedious. If there were multiple commas at random places in text, above formula would be useless. Using above formula, we can only separate text on the first appearance of specific text in string. Split text at specific character/s using Text To Column Feature If you want, you can value paste of output and delete the source data.
Using this method you will have original text intact. Whenever you will enter a new data in column, you will get extracted text in adjacent columns (if you copy formula in adjacent cell). So we have split text at specific text in a string using text excel function. Now as we know that RIGHT function returns the given number of characters from right of a given string, hence we get 30 (2 characters from right). Now the formula solves to RIGHT(A2,18-16), which again resolves to RIGHT(A2,2). The FIND function returns 16, as it does previously. The LEN function returns the number of characters in the string. The Find function can then be used to return the position of your substitute string, and this position can then be supplied to the Left, Mid or Right function.Let's break it down from inside. The Substitute function substitutes the N'th occurrence of a specified string, with a second supplied string. One way to find the position of the N'th occurrence of a character is to use the Excel Substitute function, combined with the Excel Find or Search function. So what can you do if you want to split your string at the N'th space? The problem with the Excel Find and Search functions is that they can only be used to find the first occurrence of a specified character (or string of characters), after a specified start position.
Split a String at the N'th Occurrence of a Specified Character Therefore, the Right function returns the last 6 characters of the supplied string. Therefore, the expression LEN( A1 ) - FIND( " ", A1 ) evaluates to 6 (= 11 - 5), which is then supplied to the Right function. In the above formula, the Len function returns the value 11, as the length of the string "test string" and the Find function returns the value 5 as the position of the space. If you want to use a formula to split a text string at the first space, and then return the right (the end) part of the string, this can be done by combining the Right function with the Excel Find function and the Excel Len function. Subtracting 1 from this value gives the value 4, which is then supplied to the Left function.Įxample 2 - Return Text From the End of a Text String In the above formula, the Find function returns the value 5 as the position of the space within the supplied text "test string". If you want to use a formula to split a text string at the first space, and then return the left part of the split string, this can be done by combining the Left function with the Find function. Split String at a Specific Character - Examples Example 1 - Return Text From the Beginning of a Text String, Up to the First Space