• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulas to extra data after point in various different text strings

D

DMCH

New Member
Hello I am in need of some assistance with a formula that will allow me to extract account numbers that show up in a cell (cells are all in one column) the account numbers are in various positions amongst the data entered. Most times the words ACCOUNT# followed by a space appear to the left of the actual account number. To further complicate matters the accounts vary in length.

Here are examples of the cell data where I need to extract the account numbers from.

PSE&G ACCOUNT # 66 309 514 09 PROPERTY/BLDGNUM-3159 CAMPUS

NEW BRUNSWICK WATER WATER ACCOUNT # 328001940 BLDG NAME DEMAREST HALL

ATLANTIC CITY ELECTRIC ACCOUNT # 5000 9785 655 PROPERTY/BLDGNUM-SJ RESEARCH

What is the formula to use - I'm thinking mid, right, if, totally not working for me. Can someone please help me with the correct formula(s)

Thank you,
DMCH
 

DMCH​

What are expected/correct ... account numbers ... from those three lines?
? 66 / 328001940 / 5000 ... or something else?
 
Here is a Power Query solution

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," ","",Replacer.ReplaceText,{"Column1.2"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Column1.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Column1.2.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1.2.1", "Account Nrs"}})
in
    #"Renamed Columns"
 

Attachments

  • PQ Split.xlsx
    17.3 KB · Views: 1
AlanSidman said:
Here is a Power Query solution

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," ","",Replacer.ReplaceText,{"Column1.2"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Column1.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Column1.2.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1.2.1", "Account Nrs"}})
in
    #"Renamed Columns"
Click to expand...
Hello, this is too advanced for me I do not understand, sorry. Thank you for offering up suggestion
 
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
www.excelcampus.com

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.
www.excelcampus.com www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.
www.excelcampus.com

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.
www.excelcampus.com www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...
excel.solutions
 

DMCH​

Your Anyone else have a suggestion?
Usage: Paste Your data and click [ Do It ].
 

Attachments

  • DMCH.xlsb
    17.4 KB · Views: 5
Thank you I appreciate everyone's time and assistance. It is unfortunate that I am unable to run a macro on my work computer.
 
I'm not permitted by policy to enable macros. Can you send me the VBA details and perhaps I can have it "cleared" so I can implement.
 
While you believe that PQ is beyond you, have you looked at the tutorials I have provided on how to apply my Mcode?
 
Hi, No I have not looked yet. I plan to review over the weekend though. I really appreciate you sending along all the information.
 
You must log in or register to reply here.
Back
Top