Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Extract text from one column into another

Hello All - 

 

I am currently working with some Google Analytics data and attempting to extract out a specific piece of text from the URL column into another column.  

The urls are typically formatted as: 

website.com/ua/acct_pr/acs/cs_us_en/bulletins/ubs.htm?Product=acs&FirmID=324270&mybanner=1

I would like to specifically pull out the number after the FirmID=.

The tricky part is, it doesn't occur at the same spot in the string in order to count characters.  I tried to split the column with the & as the delimiter, but no such luck as it occurs in some of the rows for various reasons.

I am not the most seasoned programmer by any means, so any help would be greatly appreciated.

  • ankitpatira's avatar
    ankitpatira
    10 years ago

    Anonymous If number after FirmID= is always going to be of six characters then below will do the trick. Under modelling tab create new calculated column as below.

     

    NewColumn = MID(TABLE[URLColumn], FIND("FirmID=", TABLE[URLColumn] ) + 7, 6)

1 Reply

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Anonymous If number after FirmID= is always going to be of six characters then below will do the trick. Under modelling tab create new calculated column as below.

     

    NewColumn = MID(TABLE[URLColumn], FIND("FirmID=", TABLE[URLColumn] ) + 7, 6)