Excel Spreadsheet question

[tlbcreates]

Windows 10 - Excel through Office 365 is what is used.

I have data that I pull in from a program that is exclusive to our company (I have no control on how the data is entered in this program - I state that because I am sure this would be easier if data was entered the same way for each account)  I have been trying to learn how to in excel get it so I can manipulate the data so I can use if for my daily work with retyping all the information that is already here.

One of my struggles that I am not able to find a solution for (I have found something that works but it involves me going and doing it per each line) is breaking the address for each account into their own columns ie Address 1, address 2, city, state, zip.

Right now the account number is what makes each account unique.  You will see there is Customer Name and then below that is the address which can be anywhere from two to 5 rows and not consistent with attention line being the same row etc.  Is there anyway to get the data over to separate columns?

[#2]

@tlbcreates 

If possible it's better to transform data on importing form other program phase. But that's not clear how you do that - that's programming interface, or you see other program data on the screen and retype them in Excel or what.

Also not clear all columns are taken from outside or you take only few first columns and add the rest manually.

Plus you use next row for the same account in some other columns, they also shall be combined to one row?

[#3]

@tlbcreates 

I have been able to make some progress using Excel 365.  Without that, I would probably have turned to PowerQuery though that would have pushed my knowledge to its limits.

What I did was to use the account number as a filter and retain one row per record.

= LET(
    data,    FILTER(Main, ISTEXT(Main[Account])),
    trimmed, TRIM(SUBSTITUTE(data, CHAR(160), CHAR(32))),
    IFERROR(VALUE(trimmed), trimmed)
  )

The additional lines remove the hard spaces in order to get numbers to read correctly (though I am in the wrong locale for US dates to come out right).  Having stripped the address details, I introduced a second formula to insert 2 or 3 rows of address data to new columns.

= LET(
   filteredAcc, FILTER(Main[Account], Main[Account]<>""),
   rowNums,     XMATCH(filteredAcc, Main[Account])+{1,2,3},
   address,     INDEX(Main[ Customer Name / Service Address], rowNums),
   current,     ISBLANK(INDEX(Main[Account], rowNums)),
   IF(current, address, ""))

That looks up the filtered account numbers using XMATCH and transcribes the next 3 rows to separate columns.

[#4]

@Peter Bartholomew 

Main question shall columns on the right be editable or not.