I'm sure this question has been answered, but I haven't found the answer, despite extensive searching. I'm certainly an amateur Excel user with little experience with index/array formulas.
I am receiving ranking data for offers on a website which are categorized by detailed category node id's. The report comes like this:
11036541 = the node id (computer-security-cables)
:76 = sales ranking
So this means that the SKU [email protected]@100 is ranked #76 in sales in the computer-security-cables category.
I need to break down the SalesRank into the two separate values they represent, so
computer-security-cables are listed in separate cells. In the picture it would have the category returned in the corresponding row in C column and the sales rank in D column.
If the node id's were a uniform amount of digits this wouldn't be hard to achieve, however with various lengths and various lengths of text after
: I am unable to make it work. Any help will be greatly appreciated.
To extract anything up to 10 digits after the colon in
SalesRank 11036541:76 (assumed to be in A1) please try:
To find what is in parentheses in
11036541 = the node id (computer-security-cables) :76 = sales ranking (assumed to be in A3) please try:
Try Data / Text to Columns / Delimited / Other / ":" to use ":" as a seperator between the two fields