Forum Discussion

Costan08's avatar
Costan08
Copper Contributor
Sep 21, 2022
Solved

Expanding range stated in same cell.

Hello everybody,   I have a question regarding a data table imported from a PDF file.    I have a product catalogue where a range of codes are 'grouped' in a single cell, expressed as 'Lowest num...
excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 22, 2022

    Costan08 Perhaps the attached file is what you want. It allows for the five code structures that I mentioned earlier. It seems a rather clumsy approach, though.

     

    The combined tables from the PDF contained 2238 line-items. The query produces 14648 line-items (216 more than in my previous attempt). Difficult to be sure that this one is complete now. Check it thoroughly please!

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 22, 2022

DexterG_III Nice, but unfortunately, the color codes do have non-continues character sequences. I noticed 5 different alpha-numeric structures:

n, nCn, Cn, CnC and CnCn where n stands for a number (or number sequence) and C stands for a character (or character sequence).

So, you will find codes like A90000 and AA229A just to give an example.

Costan08's avatar
Costan08
Copper Contributor
Sep 22, 2022
I was trying to re-insert the numbers by splitting columns an re-combining them based off the knowledge I have about how the codes are structured. This is a tedious and error prone process though. Again, though, I am a power query novice so I am unsure if there is a better process to achieve the same result.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 22, 2022

    Costan08 Perhaps the attached file is what you want. It allows for the five code structures that I mentioned earlier. It seems a rather clumsy approach, though.

     

    The combined tables from the PDF contained 2238 line-items. The query produces 14648 line-items (216 more than in my previous attempt). Difficult to be sure that this one is complete now. Check it thoroughly please!

     

Resources