6

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
7

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

values

formulas

| improve this answer | |
5

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

enter image description here

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:

enter image description here

Having column E data makes all the other formulas quite simple!

| improve this answer | |
  • 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
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"))

enter image description here

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.

| improve this answer | |
  • 2
    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
0

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

| improve this answer | |
-2

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

| improve this answer | |
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.