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
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityHow 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
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:
.
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.
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!
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
.
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
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).
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
REGEXEXTRACT
– benshepherd Nov 26 at 11:36