How to Split String in Excel?

Question

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

Answer 1

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

Answer 2

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!

Answer 3

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.

Answer 4

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))

Answer 5

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).