Create new column with some data from other column if it matches a pattern

[Mikeepb]

Hi,

I'm trying to create a new column in a table using the some of the data from another existing column if it matches a pattern. I'm playing around with LEFT and SEARCH, but can't get it to work.

Data Example:

• OPC-1234567 - Random text here
• More Random text here OPC-2345678
• And even more text here OPC-3456789 with some random text on the end

If the data contains any OPC-* value, I'd like to put that, just the OPC-* in to a new column, i.e. the full OPC-<number> string.

Thank you

[EMGuyant]

The following expression may work. It assumes OPC-### is directly followed by a space and returns a blank if OPC is not found in the text string.

OPC Value = 

//Starting position of OPC in text string
VAR _opcStart = FIND("OPC", 'Table'[Data Column], 1, LEN('Table'[Data Column])+1)
//Position of the space following the OPC start position
VAR _opcEnd = FIND(" ", 'Table'[Data Column], _opcStart, LEN('Table'[Data Column])+1)
//Length of OPC-#####
VAR _opcLength = _opcEnd - _opcStart

RETURN
// Using MID allows for defining a starting position within the string (_opcStart) and a number of characters (_opcLength)
MID(
    'Table'[Data Column],
    _opcStart,
    _opcLength
)

[Mikeepb]

Existing Column	New Column
OPC-1234567 - Random text here	OPC-123456
More Random text here OPC-2345678	OPC-234567
And even more text here OPC-3456789 with some random text on the end	OPC-3456789

So if existing column has an OPC-<value> in it, I'd like to extract that OPC value in to a new column.

Many thanks

[eliasayyy · Accepted solution]

oh , im not sure i couldnt find an effiecent solution 

Result 3 = 
VAR StartPos = SEARCH("OPC-", [Existing Column], 1, LEN([Existing Column])) + 4
VAR TempString = MID([Existing Column], StartPos, 15)  -- Assuming maximum length of OPC- string is 15
VAR NumberString = 
    CONCATENATEX(
        FILTER(
            GENERATESERIES(1, LEN(TempString)),
            IFERROR(ISNUMBER(SEARCH(MID(TempString, [Value], 1), "0123456789")), FALSE)
        ),
        MID(TempString, [Value], 1),
        ""
    )
RETURN "OPC-" & NumberString

[eliasayyy]

have you tried using the containstring function 

if( containsstring([your column],"OPC") , "OPC")

[Mikeepb]

Hi, Thanks for that. I'm needing to pull in the OPC value from the existing column, whatever it may be. I'll keep plugging away.

[eliasayyy]

so you need the full value from it?