Formula(s) / macro to reduce the digit numbers length of unit measure (UM) part from a string, then to export the new UM section in another column

[vladimiratanasiu]

Hello!

I have a very large database, including about 13000 retail products. Each product, depending on the composition and packaging, includes in its string name (column A) data about weight or volume, expressed as UM in litre / kilogram or their smaller unit (miligram / mililitre)- e.g. LIPTON ICE TEA GREEN WHITE PIERSICA 1.5L ; FITNESS CEREALE FRUCTE 225G. The values corresponding to products are expressed by larger (see column A - 1250G, 1800ML etc.) or smaller (idem – 2L, 1KG etc.) values. Giving the integration of database in a SaaS software, the new technical conditions require a standardized system for names of products, with a limited number of characters. It views also the UM elements length, that must be optimized by reducing as much as possible their dimension, and converting the larger length a smaller one. In this sense, it was decided that all values exceeding the thousandth number level (e.g. MIRINDA STRUGURI SI PEPENE GALBEN 1400ML) must be reduced to a smaller word dimension (e.g. MIRINDA STRUGURI SI PEPENE GALBEN 1.4L – column Desired UM results), and other ones, with a large number of digits of UM (e.g. CHIO CHIPS DLGHT SARE 1250G), be converted to a smaller base of UM(CHIO CHIPS DLGHT SARE 1.25KG). The other values from the names of products, not exceeding the hundredth level (e.g. MIRINDA STRUGURI/PEPENE GALBEN 2L, CINI MINIS CEREALE CAPSUNI 500G, PASTA DINTI AQUAFRESH 3-5ANI 50ML etc. ), will keep their initial form. In case of products without UM (e.g. PACHET FUNERAR), they will be transposed in column Desired UM results keeping their original form of column A.

In a second phase, I need to extract separately in cells of column G that part of every converted name from column Desired UM results, showing only quantity and corresponding UM (e.g. 1.6KG, 1.5L etc.). Similarly, the cells of column G, corresponding to products without UM, will be blank. I would be very grateful if you could find a solution (formula, macro), to cover the needs from columns Desired UM results and column G.

Thank you!

Book2.xlsx
	A	B	C
1	Column A	Desired UM results	Column Q
2	Basic data content
3	METRO CHEF MURATURI ASORTATE 1600G	METRO CHEF MURATURI ASORTATE 1.6KG	1.6KG
4	MIRINDA STRUGURI SI PEPENE GALBEN 1400ML	MIRINDA STRUGURI SI PEPENE GALBEN 1.4L	1.4L
5	MIRINDA STRUGURI/PEPENE GALBEN 2L	MIRINDA STRUGURI/PEPENE GALBEN 2L	2L
6	LIPTON ICE TEA GREEN WHITE PIERSICA 1.5L	LIPTON ICE TEA GREEN WHITE PIERSICA 1.5L	1.5L
7	CHIO CHIPS DLGHT SARE 1250G	CHIO CHIPS DLGHT SARE 1.25KG	1.25KG
8	FITNESS CEREALE FRUCTE 225G	FITNESS CEREALE FRUCTE 225G	225G
9	SET 12PAHARE 1800ML	SET 12PAHARE 1.8L	1.8L
10	SANOVITA FULGI DE OVAZ 1KG	SANOVITA FULGI DE OVAZ 1KG	1KG
11	PACHET FUNERAR	PACHET FUNERAR	BLANK
12	CINI MINIS CEREALE CAPSUNI 500G	CINI MINIS CEREALE CAPSUNI 500G	500G
13	PASTA DINTI AQUAFRESH 3-5ANI 50ML	PASTA DINTI AQUAFRESH 3-5ANI 50ML	50ML
Sheet2

 

[vladimiratanasiu]

IMO, the best solution

Thank you for the answer. I was just looking for a possible solution, based on an wildcard substring. Considering the mentioned UMs (L, ML, G, KG, BUC), couldn't they make with the maximum 4 linked digits from the same substring a wildcard, defined by the syntax ****L/ML/KG/G/BUC ? It is separated by spaces from the other elements and could be clearly identified in this way within he string.

 

[Micron]

Max 4 wouldn't work, and I have no idea how any code could decide it is 4 characters or needs to be less or for that matter, even more. As evidenced by some of these rows, 0.275L would need to be 5, 80G would be 2.

ZAREA DACIC CIDRU AFINE 0.275L
SNICKERS INGHETATA CONE 70G/110ML
HAME PATE RATA 75G
RULADA 80g CU CIOCOLATA

If there is such a method that can be worked out it's beyond my capabilities I'm afraid.