# How to Split String in Excel?

How do I split what is in the first column into the 3 columns in Excel below:

``````.plnt201218P52.5    PLNT    201218  P52.5
.cyh201106P8        CYH     201106  P8
.w201106P225        W       201106  P225
.w201106C226        W       201106  C226
``````
• Do all the strings contain a 6 digit number like in the picture?? – Gary's Student Nov 20 at 12:24
• yes but strings have different lengths – Gaby Nov 20 at 12:46
• It's worth pointing out that you can do this quite easily in Google Sheets with `REGEXEXTRACT` – benshepherd Nov 26 at 11:36

Combining what others have suggested into a more complete answer:

If your data starts at `A1`, you can use the following formulas:
`B1`: `=UPPER(MID(A1,2,MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1),LEN(A1)))-2))`
`C1`: `=MID(A1,2+LEN(B1),6)`
`D1`: `=MID(A1,LEN(B1)+8,LEN(A1))`

This assumes the following are all true:

• Your data starts with a character you want to discard: `.`
• The first numerical digit starts a 6-digit number you want to extract
• You also want to extract everything to the left and right of that number (except the first character)
• You want to capitalize the first extracted string  Select the cells you want to parse and run this short VBA macro:

``````Sub ParseWeirdData()
Dim cell As Range, v As String
Dim L As Long, i As Long, CH As String
For Each cell In Selection
v = Mid(cell.Value, 2)
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If IsNumeric(CH) Then
Exit For
End If
Next i
cell.Offset(0, 1).Value = Mid(v, 1, i - 1)
cell.Offset(0, 2).Value = Mid(v, i, 6)
cell.Offset(0, 3).Value = Mid(v, i + 6, 99)
Next cell
End Sub
`````` This code assumes that the second item in the parse is a six digit numeric.

How it Works:

The code looks for the first numeric character.

• the first item is everything to the left of the first numeric character
• the second item is the first numeric character and the 5 following characters
• the third item is everything after the second item

EDIT#1:

It is very simple to avoid VBA. The key idea is to get the position of the first numeric.. In E1 enter:

``````=MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1 & "0123456789",1))
``````

and copy downwards (this "helper" column gives the position of the first numeric character).

In B1 enter:

``````=MID(A1,2,E1-2)
``````

and copy downwards. In C1 enter:

``````=MID(A1,E1,6)
``````

and copy downwards. In D1 enter:

``````=MID(A1,E1+6,99)
``````

and copy downwards: Having column E data makes all the other formulas quite simple!

• the vba works fine thanks however what would the formulas in MS Excel be for Left, Mid and Right to get the same solution – Gaby Nov 20 at 13:08
• @Gaby Easy....see my EDIT#1 – Gary's Student Nov 20 at 13:26

Just for fun, if one has Excel O365, you can use the following in `B1`:

``````=TRANSPOSE(FILTERXML("<t><s>"&LET(B,MATCH(58,CODE(MID(A1,SEQUENCE(LEN(A1),,2),1)),-1)+1,C,B+6,REPLACE(REPLACE(MID(A1,2,LEN(A1)),C,0,"</s><s>"),B,0,"</s><s>"))&"</s></t>","//s"))
``````

Or:

``````=TRANSPOSE(FILTERXML("<t><s>"&LET(PRT,MATCH(58,CODE(MID(A1,SEQUENCE(LEN(A1),,2),1)),-1),TEXTJOIN("</s><s>",,MID(A1,2,PRT),MID(A1,PRT+2,6),MID(A1,8+PRT,99)))&"</s></t>","//s"))
`````` Now drag the formula down to `B4`.

I'll further explain the 2nd formula as this beats the 1st one in code-lingo by a whopping 2 chars!

Core of the formula is `LET()`, currently available in Excel O365 and designed to hold custom variables for further calculation. In the first argument of the function we give our 1st variable a custom name, in this case "PRT". In the second parameter we calculate this variable. Since we are using Excel O365 I've used `SEQUENCE()` to part out the entire string from the second character onwards using `MID()`, `CODE()` and `MATCH()` to return the position of the first character that is larger than ASCII-code 58. You can see why I did that here.

Now since we got a position bound to our variable, we can use this variable in the third parameter of the `LET()` function; our calculation! In the case of the 2nd formula I used `TEXTJOIN()` to create a valid `XML` formatted string. I want to do this because I can use `FILTERXML()` to return an array of values from a string, a sort of "split" function one can say. For further information on that I want to refer you to this Q&A on StackoverFlow.

The last step is to `TRANSPOSE()` the returned array down into column `B:D`.

• Great answer, a bit of explanation could further improve it – Máté Juhász Nov 20 at 16:42
• `LET` is still beta, so not available for everybody – Máté Juhász Nov 20 at 16:53
• @MátéJuhász, thanks for the enthusiasm. I have given some textual explaination. – JvdV Nov 21 at 10:38
• @MátéJuhász `Let` has been available to home (monthly update) users for quite a while now. So it is out of beta. – Ron Rosenfeld Nov 21 at 12:38
• @RonRosenfeld Microsoft says its still in testing : support.microsoft.com/en-us/office/… – Máté Juhász Nov 21 at 14:02

You should find where the first occurrence of a digit is. Then use LEFT to get the first part, MID to get the following six digits, and RIGHT to get the rest of the string. You may also need the LEN function.

To get the position of the first digit on LibreOffice Calc you need to enable regular expressions in formulas and then use `=SEARCH("[0-9]";A1)`

On Excel use something like `=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1))`

EDIT: this does not work for the original question as intended (see comments). Leaving it as an alternative for someone googling "how to split string in excel" for a solution without formulas. This will only work if the input data can be cut along the same character positions (fixed lengths).

Highlight column A, click data > text to columns and follow the prompts carefully. Excel splits the data either by fixed width (ie. every 10 characters, then every 5 characters etc) or by delimiter (i.e. after space or hidden tab characters - which looks like a normal space character so try it).

• FWIW, fixed width will not work. `PLNT`, `CYH`, and `W` all go in the first column. `P52.5`,`P225`, and `P8` all go in the third. – Engineer Toast Nov 20 at 12:54
• My bad, I misunderstood the question. I thought the example provided was the source data - not the desired outcome - i.e. this will only work if all the data (all four "columns") were text stuck in column A and you wanted to split that across multiple columns. – Mobus Nov 21 at 5:28