Search code examples
excelvbadictionaryformuladata-cleaning

Get the clean data using dictionary

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:

Input

I need:

Output

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 - ",".

Solution

  • 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

    enter image description here

    Use it like =RX(A2) etc...