Data transformation spell checking words in middle of concatenated string?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi,

We have 100’s of 1,000’s of Drawings in a bespoke database that’s going to be ported to a more modern CAD PDM system.

Would like to concatenate 2 x 61 letter fields (Doc Name1 & Doc Name 2) into one, which is easy enough, HOWEVER, some words that didn’t fit between fields have simply been wrapped into both fields.

SOME records take up the full 61 characters in the first field, and the second field is the2^nd line, requiring a space to be added.

Oh, and words maybe abbreviated, &/or colloquial words too!

Examples outlined red in attached file.

Is there a way to concatenate, reverse search a space and check if the next “word” is “actually a word” (in a custom dictionary)?

I’m thinking of a staged process, adding flags to a new “status” field and using a special setup Spell check dictionary to check against (adding colloquial words as required).

Exporting to Excel took days & weeks of batch downloads before collating into one spreadsheet.

Power BI seems powerful enough, but unsure if it can spell check data

Oh, and final output needs to be xml tagged too.

Any help would be appreciated.