by The tutorial explains how to split cells in Excel using formulas and the Split Text feature. You will learn how to separate text by comma, space or any other delimiter, and how to split strings into text and numbers.
Splitting text from one cell into several cells is the task all Excel users are dealing with once in a while. In one of our earlier articles, we discussed how to split cells in Excel using the Text to Column feature and Flash Fill. Today, we are going to take an in-depth look at how you can split strings using formulas and the Split Text tool.
How to split text in Excel using formulas
To split string in Excel, you generally use the LEFT, RIGHT or MID function in combination with either FIND or SEARCH. At first sight, some of the formulas might look complex, but the logic is in fact quite simple, and the following examples will give you some clues.
Split string by comma, semicolon, slash, dash or other delimiter
When splitting cells in Excel, the key is to locate the position of the delimiter within the text string. Depending on your task, this can be done by using either case-insensitive SEARCH or case-sensitive FIND. Once you have the delimiter's position, use the RIGHT, LEFT or MID function to extract the corresponding part of the text string. For better understanding, let's consider the following example.
Supposing you have a list of SKUs of the Item-Color-Size pattern, and you want to split the column into 3 separate columns:
- To extract the item name (all characters before the 1st hyphen), insert the following formula in B2, and then copy it down the column:
=LEFT(A2, SEARCH("-",A2,1)-1)In this formula, SEARCH determines the position of the 1st hyphen ("-") in the string, and the LEFT function extracts all the characters left to it (you subtract 1 from the hyphen's position because you don't want to extract the hyphen itself).
- To extract the color (all characters between the 1st and 2nd hyphens), enter the following formula in C2, and then copy it down to other cells:
=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
In this formula, we are using the Excel MID function to extract text from A2.
The starting position and the number of characters to be extracted are calculated with the help of 4 different SEARCH functions:
- Start number is the position of the first hyphen +1:
SEARCH("-",A2) + 1 - Number of characters to extract: the difference between the position of the 2nd hyphen and the 1st hyphen, minus 1:
SEARCH("-", A2, SEARCH("-",A2)+1) - SEARCH("-",A2) -1
- Start number is the position of the first hyphen +1:
- To extract the size (all characters after the 3rd hyphen), enter the following formula in D2:
=RIGHT(A2,LEN(A2) - SEARCH("-", A2, SEARCH("-", A2) + 1))In this formula, the LEN function returns the total length of the string, from which you subtract the position of the 2nd hyphen. The difference is the number of characters after the 2nd hyphen, and the RIGHT function extracts them.
In a similar fashion, you can split column by any other character. All you have to do is to replace "-" with the required delimiter, for example space (" "), comma (","), slash ("/"), colon (";"), semicolon (";"), and so on.
Tip. In the above formulas, +1 and -1 correspond to the number of characters in the delimiter. In this example, it's a hyphen (1 character). If your delimiter consists of 2 characters, e.g. a comma and a space, then supply only the comma (",") to the SEARCH function, and use +2 and -2 instead of +1 and -1.
How to split string by line break in Excel
To split text by space, use formulas similar to the ones demonstrated in the previous example. The only difference is that you will need the CHAR function to supply the line break character since you cannot type it directly in the formula.
Supposing, the cells you want to split look similar to this:
Take the formulas from the previous example and replace a hyphen ("-") with CHAR(10) where 10 is the ASCII code for Line feed.
- To extract the item name:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1) - To extract the color:
=MID(A2, SEARCH(CHAR(10),A2) + 1, SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1) - To extract the size:
=RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))
And this is how the result looks like:
How to split text and numbers in Excel
To begin with, there is no universal solution that would work for all alphanumeric strings. Which formula to use depends on the particular string pattern. Below you will find the formulas for the two common scenarios.
Split string of 'text + number' pattern
Supposing, you have a column of strings with text and numbers combined, where a number always follows text. You want to break the original strings so that the text and numbers appear in separate cells, like this:
The result may be achieved in two different ways.
Method 1: Count digits and extract that many chars
The easiest way to split text string where number comes after text is this:
To extract numbers, you search the string for every possible number from 0 to 9, get the numbers total, and return that many characters from the end of the string.
With the original string in A2, the formula goes as follows:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
To extract text, you calculate how many text characters the string contains by subtracting the number of extracted digits (C2) from the total length of the original string in A2. After that, you use the LEFT function to return that many characters from the beginning of the string.
=LEFT(A2,LEN(A2)-LEN(C2))
Where A2 is the original string, and C2 is the extracted number, as shown in the screenshot:
Method 2: Find out the position of the 1st digit in a string
An alternative solution would be using the following formula to determine the position of the first digit in the string:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Once the position of the first digit is found, you can split text and numbers by using very simple LEFT and RIGHT formulas.
To extract text:
=LEFT(A2, B2-1)
To extract number:
=RIGHT(A2, LEN(A2)-B2+1)
Where A2 is the original string, and B2 is the position of the first number.
To get rid of the helper column holding the position of the first digit, you can embed the MIN formula into the LEFT and RIGHT functions:
Formula to extract text:
=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
Formula to extract numbers:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Split string of 'number + text' pattern
If you are splitting cells where text appears after number, you can extract numbers with the following formula:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
The formula is similar to the one discussed in the previous example, except that you use the LEFT function instead of RIGHT to get the number from the left side of the string.
Once you have the numbers, extract text by subtracting the number of digits from the total length of the original string:
=RIGHT(A2,LEN(A2)-LEN(B2))
Where A2 is the original string and B2 is the extracted number, as shown in the screenshot below:
Tip. To get number from any position in a text string, use either this formula or the Extract tool. Or you can create a custom function to split numbers and text into separate columns.
This is how you can split strings in Excel using different combinations of different functions. As you see, the formulas are far from obvious, so you may want to download the sample Excel Split Cells workbook to examine them closer.
If figuring out the arcane twists of Excel formulas is not your favorite occupation, you may like the visual method to split cells in Excel, which is demonstrated in the next part of this tutorial.
How to split cells in Excel with Split Text tool
An alternative way to split a column in Excel is using the Split Text feature included with our Ultimate Suite for Excel, which provides the following options:
To make things clearer, let's have a closer look at each option, one at a time.
Split cells by character
Choose this option whenever you want to split the cell contents at each occurrence of the specified character.
For this example, let's the take the strings of the Item-Color-Size pattern that we used in the first part of this tutorial. As you may remember, we separated them into 3 different columns using 3 different formulas. And here's how you can achieve the same result in 2 quick steps:
- Assuming you have Ultimate Suite installed, select the cells to split, and click the Split Text icon on the Ablebits Data tab.
- The Split Text pane will open on the right side of your Excel window, and you do the following:
- Expand the Split by character group, and select one of the predefined delimiters or type any other character in the Custom box.
- Choose whether to split cells to columns or rows.
- Review the result under the Preview section, and click the Split button.
Tip. If there might be several successive delimiters in a cell (for example, more than one space character), select the Treat consecutive delimiters as one box.
Done! The task that required 3 formulas and 5 different functions now only takes a couple of seconds and a button click.
Split cells by string
This option lets you split strings using any combination of characters as a delimiter. Technically, you split a string into parts by using one or several different substrings as the boundaries of each part.
For example, to split a sentence by the conjunctions "and" and "or", expand the Split by strings group, and enter the delimiter strings, one per line:
As the result, the source phrase is separated at each occurrence of each delimiter:
Tip. The characters "or" as well as "and" can often be part of words like "orange" or "Andalusia", so be sure to type a space before and after and and or to prevent splitting words.
And here another, real-life example. Supposing you've imported a column of dates from an external source, which look as follows:
5.1.2016 12:20
5.2.2016 14:50
This format is not conventional for Excel, and therefore none of the Date functions would recognize any of the date or time elements. To split day, month, year, hours and minutes into separate cells, enter the following characters in the Split by strings box:
- Dot (.) to separate day, month, and year
- Colon (:) to separate hours and minutes
- Space to separate date and time
Hit the Split button, and you will immediately get the result:
Split cells by mask (pattern)
Separating a cell by mask means splitting a string based on a pattern.
This option comes in very handy when you need to split a list of homogeneous strings into some elements, or substrings. The complication is that the source text cannot be split at each occurrence of a given delimiter, only at some specific occurrence(s). The following example will make things easier to understand.
Supposing you have a list of strings extracted from some log file:
What you want is to have date and time, if any, error code and exception details in 3 separate columns. You cannot utilize a space as the delimiter because there are spaces between date and time, which should appear in one column, and there are spaces within the exception text, which should also appear in one column.
The solution is splitting a string by the following mask: *ERROR:*Exception:*
Where the asterisk (*) represents any number of characters.
The colons (:) are included in the delimiters because we don't want them to appear in the resulting cells.
And now, expand the Split by mask section on the Split Text pane, type the mask in the Enter delimiters box, and click Split:
The result will look similar to this:
Note. Splitting string by mask is case-sensitive. So, be sure to type the characters in the mask exactly as they appear in the source strings.
A big advantage of this method is flexibility. For example, if all of the original strings have date and time values, and you want them to appear in different columns, use this mask:
* *ERROR:*Exception:*
Translated into plain English, the mask instructs the add-in to divide the original strings into 4 parts:
- All characters before the 1st space found within the string (date)
- Characters between the 1st space and the word ERROR: (time)
- Text between ERROR: and Exception: (error code)
- Everything that comes after Exception: (exception text)
I hope you liked this quick and straightforward way to split strings in Excel. If you are curious to give it a try, an evaluation version is available for download below. I thank you for reading and hope to see you on our blog next week!
Available downloads
Excel Split Cells formulas (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)
306 comments
I'm looking to split Name, address, city, state, zip in their own columns. As of now, they are in 1 column
Name Address City, State Zip
Thank you for any help!!
HI, id really love your help on this
I'm trying to split the below into columns, I'm doing ok with the =LEFT formula but trying to split the rest is proving difficult
WIFI:T:WPA;S:RUT240_93AD;P:k2T6Vvu5;;SN:1107473141;I:861585044986089;M:001E422B93AB;B:081;
For example, anything after the ":" is what im trying to get.
Eg - values ONLY after S: RUT240_93AD, P: k2t6Vvu5, SN: 1107473141
I would like just the model and serial numbers in their own column, without the "S:" attached to it
Hello!
If I got you right, the formula below will help you with your task:
=MID(A1,SEARCH("S:",A1,1)+2,SEARCH("I:",A1,SEARCH("S:",A1,1))-3-SEARCH("S:",A1,1))
I hope my advice will help you solve your task.
Hi,
I have a scenario where I get string followed by numbers as below in a cell. Sometimes it won't be in sequential order. I am able to split it taking ";" as delimiter. But not able to sort and find min value, max value as it is a string followed by number.
Integration Sprint 34;Integration Sprint 35;Integration Sprint 36;Integration Sprint 37;Integration Sprint 38;Integration Sprint 39;Integration Sprint 40
I need to split it considering ";" as delimiter and sort it in sequential order.
Then i need to find minimum value and max value
Could you please suggest excel tips for it
Hi!
We have tools that can solve your task in a couple of clicks - Split Tool and Extract tool. You can split text into rows with semicolon delimiters, and then extract numbers from each cell.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hello,
I have this table
ID(size-no.of purchase)
2-1
2-1
2-4
1-1,2-1,13-1
2-1
2-1
10-1
1-1
2-1
2-1
3-1
3-1
2-1
3-1
3-2
5-1,10-1,1-1,6-1
3-1
3-1
3-1
3-1
2-1,1-1,3-1
3-1
3-1
3-1
2-1,1-1
1-1
3-1
5-1
5-1
5-1
5-1
2-1
2-1
2-1
1-1
5-1
2-1
1-1
2-1
5-1
5-1
1-1,3-2,2-1,13-1
2-1,1-1,3-1
2-1,1-1,3-1
3-1
1-1,3-1,2-1
5-1
5-1,10-1
2-1,1-1
2-1,1-1
5-2
1-1
3-1
5-2
1-2,2-2
1-1
3-1
10-1,1-1
1-1,3-1,2-1
1-1,3-1,2-1,13-1
1-1,2-1,3-1
5-1,10-1,1-1,6-1,3-1,9-1
5-1
how to split and find sum of purchase?
Thanks for Response!
Hi!
Explain what this data means and by what numbers you want to find the amount.
Please I want to separate N1,N2,N3 up to N48 in a cell to 48 cells
Hello!
Use the Split Text feature as described above. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hey there.
I have a text like this in one cell:
"ABC: fdsdfsfdsdfds.ghfhghjhf.BCD: ghgfhfhhgfg.CDE: tretrerter.DEF: hgfhgfhggf."
where those labels (ABC, BCD, CDE, DEF) can be a single or multiple words and they are always followed by ':'.
What I'd like to get is
1) to split those blocks in different columns
2) to get the label for each of these new columns.
So it would be something like:
label1: "ABC"
text1: "fdsdfsfdsdfds.ghfhghjhf." (it's ok also to get "ABC: fdsdfsfdsdfds.ghfhghjhf.")
label2: "BCD"
text2: "ghgfhfhhgfg." (it's ok also to get "BCD: ghgfhfhhgfg.")
label3: "CDE"
text3: "tretrerter." (it's fine ok to get "CDE: tretrerter.")
label4: "DEF"
text4: "hgfhgfhggf." (it's ok also to get "DEF: hgfhgfhggf.")
I'm having problems detecting the delimiter for splitting, given that it can't be always (as you can see ABC should result = fdsdfsfdsdfds.ghfhghjhf.). So delimiter should be but only when it's followed by ':' prior to ...
Any idea about how I can do it?
Thanks.
Hi!
The separator, in your case, is the text between period and colon. But in your data, the period is both a separator and a regular character at the same time. I don't think I can help you.
For the string 100005.5559Dress, (IN CELL A2)
using the = LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),
output is as followed:
100005.555
NOT 100005.5559.
Please advise an amended code to have 100005.5559 reflected as the output.
Hello!
You need to add one more symbol to your formula: "."
=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","."},""))))
This should solve your task.
Hi Folks,
This helped me with some of my task, but there is one section I can't seem to extract. I have a string from a barcode scanner that looks like this:
B55032324d12200420222537\r\n31483\r\n086-118090-900\r\nNWK12200420222536
And I want to break it apart at the "\r\n" in separate cells, the order or orientation is not important.
Hello!
Writing a formula to separate 4 words is a very difficult task. I recommend split text with the Split Text feature.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Thanks !!! you saved my life <3
Super useful. I used it to separate out the domain in email addresses so I could see which emails were in the same domain like this....
=LEFT(B2, SEARCH("@",B2,1)-1)
=RIGHT(B2,LEN(B2)- SEARCH("@",B2,1))
tabel Size w h A W H B&c W H D Or D&E
Freezer : A - W: 48” x H: 28” | B & C - W: 26” x H: 30”|D - W: 48” x H: 26” 48 28 1 26 30 2 48 26 1
Freezer : A - W: 64” x H: 29” | B & C - W: 27.5” x H: 21”D & E - W: 28” x H: 39” 64 29 1 27.5 21 2 28 39 2
Freezer : A - W: 61” x H: 25” | B & C - W: 23” x H: 21”D & E - W: 25” x H: 29” 61 25 1 23 21 2 25 29 2
Thank you!!!
I have applied the following formula to my spreadsheet to split 1 cell that contains the information as follows:
Cell = 3^4^12
With the formula applied, the cell is split and separated into their own separate cells as follows:
3
4
1
The issue is that the formula I've applied does not taken into account that there may be numbers of more than 1 digit.
So instead of the intended outcome being:
3, 4, 12, the formula splits it as 3, 4 and 1
Please advise if there is a way to solve this issue?
formula to split 1st number:
=LEFT(D3,SEARCH("^",D3)-1)
formula to split 2nd number:
=IFERROR(MID(D3,SEARCH("^",D3)+1,SEARCH("",D3,SEARCH("^",D3)+1)-SEARCH("^",D3))," ")
formula to split 3rd number:
=IFERROR(MID(D3,SEARCH("^",D3)+3,SEARCH("",D3,SEARCH("^",D3)+1)-SEARCH("^",D3))," ")
Hello!
To split the text into 3 parts, you can use this tutorial: How to split full name to first, last, and middle name.
If this task needs to be solved often, I'd recommend you to have a look at our Split Names tool.
I hope it’ll be helpful.
INPUT DATA
Column A
(Blank)
(Blank)
ABCDEFGHI
JKLMNO
PQRS
UVWX
NEED OUTPUT
COLUMN B
ABCDEFGHI
COLUMN c
JKLMNO
COLUMN D
PQRS
COLUMN e
UVWX
Hi!
If I understand your task correctly, the following tutorial should help: How to convert rows to columns in Excel (transpose data).
Hi. I have the below data, each containing information separated by the "^" sign.
The length of this data is not consistent, with some containing 2 "^" and others with up to 15 "^"
I've tried the LEFT, MID, RIGHT formula, but I do not know how to extend it to search up to 15 "^"
Please can you help.
Data:
Colour (heading)
Orange^Black^Red^Yellow^Grey
Colour Reference (heading)
O^BL^R^Y^GY
Colour QTY to Order (heading)
1000^1500^1300^500^2000
I would like to split the data in each row, into it's own column/row
So that in the end:
Colour QTY to Order
1000 - each in it's own row/column
1500
1300
500
2000
Hello!
To split text into cells, you can use Split Text feature. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
You can use the Text to Columns tool. Read more about it in this article.
I hope it’ll be helpful.
Thank you for the quick reply.
I was hoping there was a formula I could use, so that when the s/sheet is auto-populated with data, I can then add a formula that will automatically split the columns, as opposed to using Text to Columns.
Please let me know.
Thanks
Joanne
Hi Joanne,
Assuming the titles of the columns are the values you are searching for you can get your table with a single formula.
Titles like O, BL, R, Y, GY in B1:Z1
Data into A2
O^BL^R^Y^GY
B^GR^1000
B^GR^Y
RD^5000
1000^5000 etc
Into B2 put the following :
=IFERROR(IF(FIND("^"&B$1&"^","^"&$A2&"^")>0,B$1,""),"")
drag formula to cover span of the table.
I've added manually ^ to each end of the string to search to ensure all values are found (in case one end is missing a character)
If it finds the ^xx^ in the string it puts it in the matching titles column.
After using Python for years, it almost seems criminal that Excel doesn't include a "split" command within the UI. I created my own user-defined function (UDF) to accomplish this. This parser can pluck the Nth element from a given string provided there's a consistent delimiter, and it can work left to right or right to left. Code:
Option Explicit
Function GetLength(a As Variant) As Integer
If IsEmpty(a) Then
GetLength = 0
Else
GetLength = UBound(a) - LBound(a) + 1
End If
End Function
Function FetchElement(RefCell As String, ReturnElementNbr As Integer, Delimiter As String, Reverse As Boolean) As String
Dim Hierarchy As String
Dim MyArray As Variant
Dim ArraySize As Integer
If Reverse = True Then
If Right(RefCell, 1) = Delimiter Then
Hierarchy = StrReverse(Left(RefCell, Len(RefCell) - 1)) 'remove trailing "\" and then reverse remainder of string
Else: Hierarchy = StrReverse(RefCell)
End If
Else: Hierarchy = RefCell
End If
MyArray = Split(Hierarchy, Delimiter)
ArraySize = GetLength(MyArray)
If ReturnElementNbr > ArraySize Then
FetchElement = ""
ElseIf Reverse = True Then FetchElement = StrReverse(MyArray(ReturnElementNbr - 1))
Else: FetchElement = MyArray(ReturnElementNbr - 1)
End If
End Function
sample data to parse (let's say it's in cell "A1"): prvd00664966\PT00076084\PT00072170\PT00072157\PT00076116
command: =FetchElement(A1,3, "\", TRUE)
this will count the elements from right to left (TRUE flag used) with an assumption that the delimiter is a "\" and that we want the third element (parm = 3, above) in the list.
Yes, UDFs are relatively slow. But this is a heck of a lot more readable and usable than having to use so many nested string functions in Excel
Thank you for your instructions. Unfortunately I could not find my answer. If you could be so kind to help me. I have the following data:
For example (123)
For example (1)
For (example) (344)
For example (for) example (12)
I need to remove the numbers and the brackets. I tried many ways but I have not found a solution. There is a formula for removing everything after and including brackets. However then I have a problem with data such as: For example (for) example (12) . In which it will not only delete the numbers but also the text.
Is there a formula for deleting numbers in brackets? Or a way for me to remove it?
Hope to hear from you soon.
Hello!
Here is the formula that should work perfectly for you:
=SUBSTITUTE(SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER( --MID(A1,ROW($1:$93),1))), MID(A1,ROW($1:$93),1),"")))," ",""),"()","",1)
We have a tool that can solve your task in a couple of clicks — Ablebits Data - Remove Characters. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi There,
How do I separate text by a pattern. However, the pattern can change and there is no common string that I can use to separate the cell into columns.
For example,
I have the text 2015 Mazda CX5 V6 White Bumper. I would like to split this by Make, Model, Year, Engine Type, Color, Part and others if any. The tricky part here is on the next row I could have 2003 Toyota Camry Bonnet Blue. I want all the makes to go into 1 column, all the models to go into 1 column, all the parts to go into 1 column and so on. The pattern can change as well. Because sometimes it can start with the make and end with model or it could be other as well. I want this process to be automated.
Because the excel wouldn't understand what the make, model, year are, what we could do is we could probably add a drop-down on the heading of each column that has the list of makes, and another column would be models and so on. So, now when the system identifies make, it would have to search in all the drop-downs of all the columns and find where it is and put it on that column. I know it's a little complex, but I am sure it can be done.
Any help would highly be appreciated. Thank you!!
The goal is to split each Biblical Hebrew word into individual letters. However, Text-to-Column causes the nikkud/accents to separate from the base letter.
I've discovered that putting a hyphen between each letter of the Hebrew word will cause Text-to-Column to separate the individuals characters intact.
So, I went looking for a formula to insert dashes between letters and found one that works....but only for English letters/numbers. With the Hebrew, this formula separates out the nikkud/accents.
https://superuser.com/questions/1371473/how-can-i-format-a-character-string-in-excel-to-insert-hyphens
Sub InsertCharacter()
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Dim xRow As Integer
Dim xChar As String
Dim index As Integer
Dim arr As Variant
Dim xValue As String
Dim outValue As String
Dim xNum As Integer
xTitleId = "Put Dashes"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
xRow = Application.InputBox("Number of characters :", xTitleId, Type:=1)
xChar = Application.InputBox("Specify a character :", xTitleId, Type:=2)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
xNum = 1
For Each Rng In InputRng
xValue = Rng.Value
outValue = ""
For index = 1 To VBA.Len(xValue)
If index Mod xRow = 0 And index VBA.Len(xValue) Then
outValue = outValue + VBA.Mid(xValue, index, 1) + xChar
Else
outValue = outValue + VBA.Mid(xValue, index, 1)
End If
Next
OutRng.Cells(xNum, 1).Value = outValue
xNum = xNum + 1
Next
End Sub
Question: Is there a way to tweak this formula so that it will recognize Hebrew 'complete characters'? Or is there another way to work with Hebrew in Excel?
Thanks so much.
problem string:
/subscriptions/dummy-subscription-id/resourceGroups/my-resource-group-name/providers/Microsoft.Web/serverfarms/my-app-service-plan-name
what i needed out of it?
my-app-service-plan-name
what expression got it?
=RIGHT(I2,LEN(I2)-SEARCH("serverfarms",I2)-LEN("serverfarms"))
thanks for guiding me towards
Super thanks to you, you made my day really grate
whith this formular :
=LEFT(A10,LEN(A10)-LEN(D10))