Search code examples
excelworksheet-function

Excel 2013 create column w/specific value from another column?

I have a spreadsheet with a column "application url" (this is a feed from Apple). Within that column/url is the id number of the product:

http://itunes.apple.com/app/pntool/id852499288?uo=5

The above is a standard example of the data in the column "application url".

What I'd like to do is create a new column A (moving the existing column "a" to column "b", "b" to "c", etc…) titled "ID" and populate it with just the id number (the number following "id" but preceeding the "?").

So that I end up with:

Column A

ID

777888999

And, of course, each row's ID would match that with the id found in that row's "application url" column.

Solution

  • If B will be new column that the url is in, then use the following formula in A, and drag down.

    =MID(B2, SEARCH("id",B2)+2, SEARCH("~?",B2)-SEARCH("id",B2)-2)