Extract text from one column into another

[Anonymous]

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 · Accepted solution]

@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)