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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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,391
Messages
6,171,820
Members
452,426
Latest member
cmachael

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