Power Query - Remove every N characters in field/column

dcbuzzell

New Member
Joined
Jan 22, 2013
Messages
36
I have a field in my data that is actually comprised of multiple fields from the original data (which I can't access). A series of 70 character strings are strung together to create my field, but in the process a space is added between the original fields. I'd like to figure out how I can remove that space that occurs after 70 characters - so characters, 71, 142, 213, etc. in each record of a column.

An example string/record:
Clean out sump closet
Clean exterior flat work
Clean unfinished baseme nt area
Clean out cabinets at basement bar, clean countertops
Clean tr acks of sliding glass doors throughout, difficult to operate
Clean fro nt porch, check for defects

In essence, I want to fix the spelling of "basement," "tracks," and "front"

I apologize if this was posted elsewhere, my search didn't turn up results.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Book1
ABCD
1Column1Column1
2Clean out sump closet Clean exterior flat work Clean unfinished baseme nt area Clean out cabinets at basement bar, clean countertops Clean tr acks of sliding glass doors throughout, difficult to operate Clean fro nt porch, check for defectsClean out sump closet Clean exterior flat work Clean unfinished basement area Clean out cabinets at basement bar, clean countertops Clean tracks of sliding glass doors throughout, difficult to operate Clean front porch, check for defects
3
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each 
            let 
                len = Text.Length(_),
                lst = List.Reverse(List.Generate(()=>70, (x)=> x <= len, (x)=> x + 71))
            in  
                List.Accumulate(lst, _, (s,c)=> Text.RemoveRange(s,c,1))
    })
in
    tbl
 
Upvote 1
Solution
Book1
ABCD
1Column1Column1
2Clean out sump closet Clean exterior flat work Clean unfinished baseme nt area Clean out cabinets at basement bar, clean countertops Clean tr acks of sliding glass doors throughout, difficult to operate Clean fro nt porch, check for defectsClean out sump closet Clean exterior flat work Clean unfinished basement area Clean out cabinets at basement bar, clean countertops Clean tracks of sliding glass doors throughout, difficult to operate Clean front porch, check for defects
3
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.TransformColumns(Source, {"Column1", each
            let
                len = Text.Length(_),
                lst = List.Reverse(List.Generate(()=>70, (x)=> x <= len, (x)=> x + 71))
            in 
                List.Accumulate(lst, _, (s,c)=> Text.RemoveRange(s,c,1))
    })
in
    tbl
Excellent! Thank you very much. I thought right after that I could split the column into 8 separate columns, trim the last character, then combine it back, but this is obviously much cleaner. I'll have to study to understand just what is going on here. This solves a minor irritant, much appreciated!
 
Upvote 0

Forum statistics

Threads
1,223,977
Messages
6,175,753
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top