I get a table where I have ID and and description, so I only need desciption as output.
DATA:
| Language | Product |
|---|---|
| 9;#JS;#20;#React | 2;#Comon |
| 11;#PL/SQL | 163;#Не участвует в продуктовой команде |
| 6;#Java;#7;#Kotlin;#8;#Solidity;#9;#JS;#10;#Dart/Flutter | 164;#Умный старт;#165;#Прочее |
| 12;#Swift/ObjectiveC | 167;#FinamTrade;#168;#SuperApp;#169;#WatchList;#170;#Профиль инструмента;#171;#Кубики;#172;#Advisor Novoadvisor;#164;#Умный старт;#173;#Технический анализ;#4;#App Банка;#188;#Личный кабинет;#174;#J2TX |
Wanted DATA:
| Language | Product |
|---|---|
| JS, React | Comon |
| PL/SQL | Не участвует в продуктовой команде |
| Java, Kotlin, Solidity, JS, Dart/Flutter | Умный старт, Прочее |
| Swift/ObjectiveC | FinamTrade, SuperApp, WatchList, Профиль инструмента, Кубики, Advisor Novoadvisor, Умный старт, Технический анализ, App Банка, Личный кабинет, J2TX |
Example:
I need:
This is a dictionary. I can solve this through Python but I have to use Excel (formulas or VBA). Also, you can see that delimeters are ";" but I need as output - ",".
CodePudding user response：
Here is a custom UDF you can call:
Function RX(s As String) As String
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "(?:^|;#)\d ;#"
RX = .Replace(Mid(s, InStr(s, ";#") 2), ", ")
End With
End Function
Use it like =RX(A2) etc...