Extracting Information from one column to a new column

[Anonymous]

I am wondering if this is possible, or if it's just wishful thinking.

I have a column that contains paragraphs of information in each cell. They are descriptions of reports. But contained within each of these descriptions (and there are hundreds of them), are report numbers like: "JS 93498390" or "JS #93498390" or "JS#93498390" Every report ID has the letter JS in front, followed by a # or a space or no space and then 8 digits like the examples shown.

I want to extract them all into a new column. I can separate them and sort them from there. Is this possible? What would happen if there were multiple report numbers contained within each line item?

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Here I suggest you to create a calculated column by below code.

Extracted Report =
VAR _Step1 =
    RIGHT (
        'Table'[Description],
        LEN ( 'Table'[Description] ) - FIND ( "JH", 'Table'[Description], 1, 0 ) + 1
    )
VAR _Step2 =
    LEFT ( _Step1, FIND ( ".", _Step1, 1, 0 ) - 1 )
RETURN
    _Step2

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.