Parsing alphanumeric data into tabular format

[manjeet]

hi experts,
I have the below data where the numbers represent the number of hours and letters represent the code. For ex: H would represent holiday, CS represents Client services etc. and the numbers before them represent the number of hours corresponding to those codes. Anything to the left of the opening bracket sign represents the available hours, where total hours per week is 40. Right of the opening bracket represents utilization. Is there a way to convert this data into a tabular format where each column represents the unique code (CS, H, V etc.) and the corresponding hours in rows:

De Bruyne	32(8H)
Gundogan	8(8CS16FP8H)
Neymar	24(8H8V)
Ronaldo	-(40CS)
Messi	-(32CS8H)
Benzema	-(8H32L)
Mo Salah	40

 

[kweaver]

Welcome to Mr. Excel.

Would you please provide the results you expect for your sample data for a better understanding of what you want?

 

[manjeet]

Welcome to Mr. Excel.

Would you please provide the results you expect for your sample data for a better understanding of what you want?

NAME	Available	IP	H	CS	FP	V	L
De Bruyne	32	0	8	0	0	0	0
Gundogan	8	0	8	8	16	0	0
Neymar	24	0	8	0	0	8	0
Ronaldo	0	0	0	40	0	0	0
Messi	0	0	8	32	0	0	0
Benzema	0	0	8	0	0	0	32
Mo Salah	40	0	0	0	0	0	0

 

[kweaver]

Well, I brute-forced myself through this and came up with:

Sub splitvalues()
Dim lr As Long, i As Long, Ostr As String, str As String, str2() As String, str3() As String, j As Long
Dim iCode As String
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2:H" & lr) = 0
For i = 2 To lr
 Ostr = Cells(i, "B")
 Ostr = Right(Ostr, Len(Ostr) - InStr(1, Ostr, "("))
 iCode = InStr(1, Ostr, ")")
  If iCode <> 0 Then
 Ostr = Left(Ostr, iCode - 1)
  Else
  End If
 str = Replace(Ostr, "CS", ",")
 str = Replace(str, "H", ",")
 str = Replace(str, "V", ",")
 str = Replace(str, "L", ",")
 str = Replace(str, "FP", ",")
 str = Replace(str, "IP", ",")

For j = 0 To 9
 Ostr = Replace(Ostr, j, " ")
Next j
 Ostr = Replace(Ostr, "  ", " ")
 
str2 = Split(Ostr, " ")
str3 = Split(str, ",")


For j = 1 To UBound(str2)

 Select Case str2(j)
 
 Case "IP"
 Cells(i, "C") = str3(j - 1)
 
 Case "H"
 Cells(i, "D") = str3(j - 1)
 
 Case "CS"
 Cells(i, "E") = str3(j - 1)
 
 Case "FP"
 Cells(i, "F") = str3(j - 1)
 
 Case "V"
 Cells(i, "G") = str3(j - 1)
 
 Case "L"
 Cells(i, "H") = str3(j - 1)
 
 
 End Select
 
Next j

Next i

End Sub

Book1
	A	B	C	D	E	F	G	H
1			IP	H	CS	FP	V	L
2	De Bruyne	32(8H)	0	8	0	0	0	0
3	Gundogan	8(8CS16FP8H)	0	8	8	16	0	0
4	Neymar	24(8H8V)	0	8	0	0	8	0
5	Ronaldo	-(40CS)	0	0	40	0	0	0
6	Messi	-(32CS8H)	0	8	32	0	0	0
7	Benzema	-(8H32L)	0	8	0	0	0	32
8	Mo Salah	40	0	0	0	0	0	0
Sheet1