• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extracting Text|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Extracting Text|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryExtracting Text
sp_PrintTopic sp_TopicIcon
Extracting Text
Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
1
April 10, 2019 - 11:46 pm
sp_Permalink sp_Print

I need to be able to filter on Section, Township Range.  The tax roll data concatenates the properties by owner rather than have a separate row or record for each property.  How in the world can you create separate records for each instance Sec-Twp-Rng?

I was hoping I could extract the strings with form at nn-nn-nn (e.g. 01-29-63).  I started my attempt trying to use wildcard ??-??-?? but that brought no joy.

This is such a jumbled mess it may be impossible.

01-29-63 LOT 01-02-03-04, S/2N/2, S/2 02-29-63 LOT 01-02, S/2NE, SE 04-29-63 S/2SW 05-29-63 SWNE,NWSW PART, NESW, S/2SW, W/2SE, SESE 06-29-63 N/2SE PART,S/2SE 07-29-63 E/2 08-29-63 N/2NE, SWNE, W/2, SE 09-29-63 W/2NE, SENE, W/2, SE 10-29-63 SWNW, S/2 11-29-63 W/2NE, SENE, E/2SE, SW, W/2SE 12-29-63 N/2, N/2SW, SWSW, SE 13-29-63 NE PART, N/2NW, S/2NW PART, SE PART 14-29-63 N/2NE, SWNE, W/2, NWSE, SESE 15-29-63 W/2NE, SENE, W/2, SE 18-29-63 N/2NE [5,886.88A]

   

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 11, 2019 - 4:22 pm
sp_Permalink sp_Print

Hi Robert,

Please provide an Excel file containing your sample data before and how you'd like it to look. I can't tell if that data is in one cell and if so, how do you want it to look? It's important that your sample data contains enough examples that every scenario is covered.

Thanks,

Mynda

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
3
April 12, 2019 - 1:59 am
sp_Permalink sp_Print

Thank you Mynda.   I tried to use a complex delimiter using numbers and symbols as a way to parse.  I searched M for wildcards but couldn't find any.

The data, as I received it, jumbles property descriptions.  The delimiter is Section-Township-Range in the form of nn-nn-nn  The attached file has the original data with an index column added.  A separate sheet illustrates the desired outcome.

Robert

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 12, 2019 - 2:20 pm
sp_Permalink sp_Print

Hi Robert,

The file didn't upload. After selecting the file, you need to click the yellow 'start upload' button.

Mynda

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
5
April 12, 2019 - 11:46 pm
sp_Permalink sp_Print

Thank you Mynda.  I think my file size was too large.  I have abridged the data.

 

Error 106 - Upload file size exceeds maximum allowed size

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
6
April 14, 2019 - 10:51 am
sp_Permalink sp_Print

Mynda:

 

I am both honored and grateful that you have chosen to address my dilemma.  My approach was to try and create a multiple letter delimiter.  As far as I could tell, the wildcards in M do not distinguish between letters and digits.  

I look forward to seeing your solution  No doubt, others face analogous scenarios.

Best regards,

Robert Cline

Smile

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
April 15, 2019 - 5:15 pm
sp_Permalink sp_Print sp_EditHistory

Hi Robert,

There is no pattern matching in Power Query yet unfortunately.

But you can build your custom pattern matching, like I did in the attached file.

What I did:

First, I created a list of items to be replaced, in this case we will replace all digits:

List1 = List.Transform({0..9}, each Text.From(_)),

Next, for each digit in List1, we need to provide a replacement. I created then a list of replacements for each item in List1 using: List.Repeat({1},List.Count(List1)). So each digit will be replaced with 1 (you can replace with any text).

ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),

In the final step, I add a new column, replacing all digits from Legal column with 1

Using Text.PositionOf ..., "11-11-11",Occurence.all) we identify the starting position of all matches for "11-11-11" pattern (any digit was replaced with 1, so our pattern is extremely simple to use now). With the list of positions provided, we simply extract from Legal column 8 chars, for each match: Text.Range(x[Legal],_,8)) . In the final step, combine all results using ";" as separator.

Here are all steps combined into 1:

#"Added Custom" = Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") ),

 

With the list of matches in this new column, all you have to do is to split this column into rows by ";" delimiter (advanced options: split into rows instead of columns)

sp_AnswersTopicAnswer
Answers Post
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
April 15, 2019 - 8:01 pm
sp_Permalink sp_Print

Ingenious, Catalin!

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
9
April 18, 2019 - 5:02 am
sp_Permalink sp_Print

Dang Catalin.  This is simply awesome.  You are an M wizard.  

 

Thank you!

Robert Cline

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
10
April 20, 2019 - 4:03 am
sp_Permalink sp_Print

Catalin:

 

I still don't get it.  I get an error "The name 'ReplacementList' wasn't recognized.  I get a new column but it doesn't know the type.  I tried changing it to as text but there was no change.  

 

= Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),"ReplacementsList"),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") )

I added Quotes to "ReplacementsList" and the code runs but get a column of errors even if I change the type to 'as text'

Do you see the error?  

Thank you.

Robert

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
April 20, 2019 - 12:34 pm
sp_Permalink sp_Print

Hi Robert,

The name ReplacementList was not recognized where? In the sample file I sent, if you read the query, you will see it, open the advanced editor:

let
Source = Excel.CurrentWorkbook(){[Name="Goshen"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account #", type text}, {"Parcel #", Int64.Type}, {"Acct Status", type text}, {"Account Type", type text}, {"Tax District", Int64.Type}, {"Name 1", type text}, {"Name 2", type text}, {"Address 1", type text}, {"Address 2", type text}, {"City", type text}, {"State", type text}, {"Zip Code", Int64.Type}, {"Legal", type text}, {"Gross Acres", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
List1 = List.Transform({0..9}, each Text.From(_)),
ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),
#"Added Custom" = Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") ),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Index", "Account #", "Parcel #", "Acct Status", "Account Type", "Tax District", "Name 1", "Name 2", "Address 1", "Address 2", "City", "State", "Zip Code", "Legal", "Gross Acres"})
in
#"Reordered Columns"

 

If it's not in your query, you should add it, copy the red lines from above into your query, before adding the new column.

Avatar
Robert Cline
Member
Members

Power Query
Level 0
Forum Posts: 9
Member Since:
December 3, 2017
sp_UserOfflineSmall Offline
12
April 21, 2019 - 12:04 am
sp_Permalink sp_Print

Got it... thank you Catalin!  

 

Robert

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: ash seth, Andrew Wilkie, Scott Miller, Les Harold
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 217
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
David du Toit
leandro barbarini
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27795

 

Member Stats:
Guest Posters: 49
Members: 32326
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.