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 C2265 Answers
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 SubThis 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!
2Just 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.
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).
2
