SOLVED

Extract Text from Cells in excel

Iron Contributor

Hi All.

 

I have a report which is run daily and from this we follow up each item if required. To help make the process easier to follow up i wish to separate the information on the cell into smaller components.

 

In the attached the reported data is in column A, and i wish to break it down into columns B to G.

 

Can someone please assist with this.

 

Thank you kindly for any assistance.

7 Replies
Hello,

You will have to use Power Query Split column to split the data
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

That could be done by Power Query, see Sheet2 in attached.

Since not all categories are in each description, and I'm not sure if you have full list of them and they are always in same order, first we convert Description into the list and from that list extract each category based on keyword. That require some coding in M-Script, entire script is

let
    Source = Excel.CurrentWorkbook(){[Name="Report"]}[Content],
    TextToList = Table.AddColumn(Source, "Custom", each Text.Split([Description],"#(lf)")),
    AddCatText = Table.AddColumn(
        TextToList,
        "Custom.1",
        each try List.Single(
            List.Select([Custom], each Text.StartsWith(_,"["))
        ) otherwise null
    ),
    AddTolText = Table.AddColumn(
        AddCatText,
        "Tolerance",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Tolerance"))
            ), "=") otherwise null
    ),
    AddCurNAVText = Table.AddColumn(
        AddTolText,
        "Current NAV",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Current"))
        ), "=") otherwise null
    ),
    AddPrevNAVText = Table.AddColumn(
        AddCurNAVText,
        "Previous NAV",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Previous"))
        ), "=") otherwise null
    ),
    AddCheckText = Table.AddColumn(
        AddPrevNAVText,
        "Check Value",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Check"))
        ), "=") otherwise null
    ),
    SplitCategory = Table.SplitColumn(
        AddCheckText,
        "Custom.1",
        Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false),
        {"Exception Category", "Exception"}
    ),
    ReplaceSemicolon = Table.ReplaceValue(
        SplitCategory,
        ";",
        "",
        Replacer.ReplaceText,
        {"Exception Category", "Exception", "Tolerance", "Current NAV", "Previous NAV", "Check Value"}
    ),
    RemoveLists = Table.RemoveColumns(
        ReplaceSemicolon,
        {"Custom"}
    )
in
    RemoveLists

Hi@Sergei Baklan 

 

Hope you are safe and well.

 

I have been looking at this example as it's very similar to a task i am currently working on, uses the same data. I have looked at the Power query code below but unsure how to update it for my current spreadsheet.

 

On the spreadsheet attached in the "data input" tab, column G is the same as the previous spreadsheet. I am hoping i can copy across the power query to extract the "Tolerance" & "Check Value" again into Column V & W.

 

if possible would please provide any notes in order to assist with this?

 

Greatly appreciate any possible assistance.

 

Stay safe, and all the best.

 

 

@calof1 

Yes, that's a similar case but not exactly the same.

 

With Power Query we can't extract fields into some cells, we may transform column G and generate from it another table with that information. The logic of such transformation is not clear for me. You have records with different fields, for example

[HLD050] - Market Price Date Check for Asset 6055112 breach of Exception was caused for entity DEFAULT; 
Market Price Date = 20190823; 
Previous Market Price Date = 20190823; 
Infrequently Priced = false;

and

[PRV030]  - Validate Movement in UOI with Capital Movement; 
Check Value = 81.3 bp; 
Tolerance Value (at Entity Level DEFAULT) = 30.0 bp; 
UOI Movement = 97115.6648;Expected UOI Movement = 97904.836498923046799;

Perhaps you may manually generate for couple of such records resulting table (or tables if they are different for different types of records) to illustrate the logic of transformation and how resulting table shall looks like. If few table - what is the logic how to separate them.

Hi@Sergei Baklan 

 

Thank you for the reply.

 

Apologies for the confusion with the logic, it was still being defined. I have now added a few columns in Y to AA. I have matched the names of the output to that mentioned in Column G. As such there is now a column for the Current Market Price Date, Previous Market Price Date & UOI Movement. I think this will cover the two queries mentioned below. From here i should have all the components to be able to workout the formulas. I have included an example of each in the first two rows of data.

 

I have noticed that for the Market Price date it is a number, is it an option to record it as a date to align with other dates? 

 

Please let me know if i can do anything further to help clarify, and once again i greatly appreciate your help. 

 

Many thanks,

 

 

@calof1 , thank you for the updated file.

 

It will be match better if you place data in Tables, but since you already have some other calculations I kept as it is. Resulting table is in columns AD:AK, in brief steps are:

 

- give names to the range we are needed. Here ColumnsBtoG are related columns, Exceptions R1:S18 and Funds Q1:Q100. If real data requires more space expand them.

- query ColumnsPtoG, split by semicolons, add Index to fix intermediate result in memory and keep sorting;

- extract Code and unpivot other than Index, Code and Entity Code columns;

- split texts on "=" and " NAV ", make other adjustments and pivot table values on texts;

- query Validation and merge previous table with it to extract Validation Check

- query Funds and merge again to extract Fund Names

- load resulting table to the Excel sheet

 

I skipped some cosmetic and adjustment steps which shall be to do, you may check other details in the file. Table looks as

image.png

Hi@Sergei Baklan 

 

Thank you again for your help with this, you are a true asset and credit to the excel community.

 

I will explore this in further detail over the weekend, wish you the best for a safe and nice weekend ahead.

 

All the best.

 

Many thanks

1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
Solution

@calof1 

That could be done by Power Query, see Sheet2 in attached.

Since not all categories are in each description, and I'm not sure if you have full list of them and they are always in same order, first we convert Description into the list and from that list extract each category based on keyword. That require some coding in M-Script, entire script is

let
    Source = Excel.CurrentWorkbook(){[Name="Report"]}[Content],
    TextToList = Table.AddColumn(Source, "Custom", each Text.Split([Description],"#(lf)")),
    AddCatText = Table.AddColumn(
        TextToList,
        "Custom.1",
        each try List.Single(
            List.Select([Custom], each Text.StartsWith(_,"["))
        ) otherwise null
    ),
    AddTolText = Table.AddColumn(
        AddCatText,
        "Tolerance",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Tolerance"))
            ), "=") otherwise null
    ),
    AddCurNAVText = Table.AddColumn(
        AddTolText,
        "Current NAV",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Current"))
        ), "=") otherwise null
    ),
    AddPrevNAVText = Table.AddColumn(
        AddCurNAVText,
        "Previous NAV",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Previous"))
        ), "=") otherwise null
    ),
    AddCheckText = Table.AddColumn(
        AddPrevNAVText,
        "Check Value",
        each try Text.AfterDelimiter(
            List.Single(
                List.Select([Custom], each Text.StartsWith(_,"Check"))
        ), "=") otherwise null
    ),
    SplitCategory = Table.SplitColumn(
        AddCheckText,
        "Custom.1",
        Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false),
        {"Exception Category", "Exception"}
    ),
    ReplaceSemicolon = Table.ReplaceValue(
        SplitCategory,
        ";",
        "",
        Replacer.ReplaceText,
        {"Exception Category", "Exception", "Tolerance", "Current NAV", "Previous NAV", "Check Value"}
    ),
    RemoveLists = Table.RemoveColumns(
        ReplaceSemicolon,
        {"Custom"}
    )
in
    RemoveLists

View solution in original post