vladimiratanasiu
Active Member
- Joined
- Dec 17, 2010
- Messages
- 347
- Office Version
- 365
- 2021
- Platform
- Windows
Hello!
I have a very large database of products. Each product is defined by a string, that includes information about its name (e.g. METRO CHEF MURATURI ASORTATE, MIRINDA STRUGURI SI PEPENE GALBEN etc. – see the red colored texts) and quantity. The quantity of the contents is defined by specific units of measure (UM - see black colored texts), depending on the type of product: weight – (kilo)grams – KG /G; volume – (mili)litre – ML /L; length – centimeters – CM; number of pieces / set - BUC. Some products are not allocated any UM (e.g. ORNAMENT MOS CRACIUN BARBA, CIOCAN LEMN PT CARNE). Most of products have the UM substrings data placed at the end of the string, but in some cases they are inserted in the middle of it (e.g. SOS USTUROI 420G UNIVER, CANA OPAL 250ML PARADISE). No name has the UM at the beginning of the string. I mention that all UM substrings have a maximum number of 4 digits, and are separated from the rest of string’s elements by space(s). Rarely, some values are expressed as decimal numbers with dots (e.g. MIRINDA STRUGURI SI PEPENE GALBEN 0.5L, ZAREA DACIC CIDRU AFINE 0.275L). I need to extract in another column the numerical data of UM substrings, defined on the basis of the above mentioned UMs. I wonder if the substrings could be defined as wildcards, built on the basis of UMs standard names. If the UM substring misses from a string, I would like result be blank or 0 in the destination cell.
Thank you!
P.S. I attached a screenshot too of the table , as the XL2BB couldn't import the original table formatted completly.
I have a very large database of products. Each product is defined by a string, that includes information about its name (e.g. METRO CHEF MURATURI ASORTATE, MIRINDA STRUGURI SI PEPENE GALBEN etc. – see the red colored texts) and quantity. The quantity of the contents is defined by specific units of measure (UM - see black colored texts), depending on the type of product: weight – (kilo)grams – KG /G; volume – (mili)litre – ML /L; length – centimeters – CM; number of pieces / set - BUC. Some products are not allocated any UM (e.g. ORNAMENT MOS CRACIUN BARBA, CIOCAN LEMN PT CARNE). Most of products have the UM substrings data placed at the end of the string, but in some cases they are inserted in the middle of it (e.g. SOS USTUROI 420G UNIVER, CANA OPAL 250ML PARADISE). No name has the UM at the beginning of the string. I mention that all UM substrings have a maximum number of 4 digits, and are separated from the rest of string’s elements by space(s). Rarely, some values are expressed as decimal numbers with dots (e.g. MIRINDA STRUGURI SI PEPENE GALBEN 0.5L, ZAREA DACIC CIDRU AFINE 0.275L). I need to extract in another column the numerical data of UM substrings, defined on the basis of the above mentioned UMs. I wonder if the substrings could be defined as wildcards, built on the basis of UMs standard names. If the UM substring misses from a string, I would like result be blank or 0 in the destination cell.
Thank you!
P.S. I attached a screenshot too of the table , as the XL2BB couldn't import the original table formatted completly.
Book1.xlsx | ||||
---|---|---|---|---|
A | B | |||
1 | Initial data | Desired results | ||
2 | ORNAMENT MOS CRACIUN BARBA | |||
3 | METRO CHEF MURATURI ASORTATE 1600G | 1600 | ||
4 | MIRINDA STRUGURI SI PEPENE GALBEN 0.5L | 0,5 | ||
5 | MIRINDA STRUGURI/PEPENE GALBEN 2L | 2 | ||
6 | IAURT CAPRA AMILACT 340ML | 340 | ||
7 | COSMIN MUSLI 30% FRUCTE 250G | 250 | ||
8 | DR.O PRAF TORT GELLE FRUCTE PADURE 8G | 8 | ||
9 | CHIO POMBAR PIZZA 40G | 40 | ||
10 | GEL MAINI AMANTE 65ML | 65 | ||
11 | FORMA TEFLON CHEC 25CM ZENKER | 25 | ||
12 | TAVA CUPTOR OTI 38X26CM | 38X26 | ||
13 | MONODOZE CAFEA CAPSULE BAROCCO 50BUCATI | 50 | ||
14 | CIOCAN LEMN PT CARNE | |||
15 | APARATE RAS GILLETTE VENUS3 3BUCATI | 3 | ||
16 | ZAREA DACIC CIDRU AFINE 0.275L | 0.275 | ||
17 | GIANA ULEI TURTE MASLINE 1000ML | 1000 | ||
18 | SOS USTUROI 420G UNIVER | 420 | ||
19 | CANA OPAL 250ML PARADISE | 250 | ||
Sheet1 |
Attachments
Last edited: