It would be helpful if you could upload a slightly bigger file with more examples of what you are dealing with and indicating how the end result should look like.

Hi,

Thanks for answering my question.  

The table was downloaded from a PDF with 2900 rows, and some of the columns got mixed up.  There are numerous problems with this download, however, the one I asked about is noted on lines 6, 7 and 22.  I wanted to move the street name only to the next column “Street,” and leave the number in the “Street No.” column. 

In this particular problem the number always comes first followed by a space, but as you will see there is more than one space in the cell.

I spent four days with ChatGPT4 and it moved the street name to a conditional column, but it couldn’t move the street name to the “Street” column.  I’m presently searching Google and Edge for a solution but no luck so far.  I think I have included the code where ChatGPT4 created a custom column and separated the names from the numerical data.  It should be noted that this code includes the separating of the person’s name in line 24.  That must go into a different column (“Voter Name”) and has different circumstances (number AFTER name).

I included the code, but I don’t think it will help because the column names are different.  If you have any questions, I’ll be glad to try to answer them.  I hope that I have attached the file correctly.

CODE

Records = Table.ToRecords(#"Added Custom to Flag"),  //previous step

   ExpandedRecords = List.Accumulate(Records, {}, (state, current) =>

    let

        newState = state & {current},

       additionalState = if current[Insert Empty Row] then

    let

        newRow = Record.FromList(

            {null, null, "Dates:", "Registered:", null, "Last Changed:", null, null, "Last Voted:", null, null, null, null},

            {"ID Number", "Voter Name", "Voter", "Column1", "Column2", "Column3", "Column4", "Column5", "Address", "City State Zip", "Moved", "Index", "Insert Empty Row"}

        )

    in

        newState & {newRow}

    else newState

in

    additionalState),

    TableFromRecords = Table.FromRecords(ExpandedRecords),

    // Function to check if a string starts with a number

    StartsWithNumber = (text as text) as logical =>

        let

            firstChar = Text.Start(text, 1),

            isNumber = List.Contains({"0".."9"}, firstChar)

        in

            isNumber,

    // Function to extract text part from a string

    ExtractText = (text as text) as text =>

        let

            numericChars = {"0".."9"},

            textPart = Text.Remove(text, numericChars)

        in

            textPart,

    // Add a column to determine if "Voter" starts with a number

    // Assuming 'PreviousStep' is the name of the step before this

    AddStartsWithNumber = Table.AddColumn(TableFromRecords, "StartsWithNumber", each StartsWithNumber([Voter])),

    //CheckAddStartsWithNumber = Table.Preview(AddStartsWithNumber),

    ExtractTextPart = Table.AddColumn(AddStartsWithNumber, "TextPart", each ExtractText([Voter])),

Please have a look and let me know the attached file including a query does what you need. I'm insecure about what I highlighted in red on row 26. Is that also a problem in your data file or is it just a typo? 

Nice job,

To answer your question, that is not an error, it is also a problem to be dealt with. 

As you can see in the Results table, that has to be moved to the "Name" column and the number has to remain in that column.  It looks like you can adapt your code to attack that, if you want

Another problem is lines 1 and 2.  The "PO" line from line 1 of the "City State Zip column has to be moved to the end of line 1 of the "Street" column.  Then the 362 on line 2 of the "Street No." column has to be moved to the end of line one of the "Street" column, then delete row 2.  That is the only occurrence so I can fix that manually. 

Thanks for your help.

Cedric

Ooops, I didn't notice the issue on rows 1 and 2. That's a difficult one to automate, though. 

I appreciate your help.  You do a great job on here.

There were so many problems with the PDF download into Excel that I decided to fix the rest manually.  Next time I'll ask for a digital copy of the information.

How is the winter in Sweden?

Cedric

"How is the winter in Sweden?"

Cold and much snow!