当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Searching partial and inconsistent lengths of text within contained cell

问题描述:

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:

SKU

[email protected]@100

SalesRank

11036541:76

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, so76 and 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.

http://tinyurl.com/l6j33b5

网友答案:

To extract anything up to 10 digits after the colon in SalesRank 11036541:76 (assumed to be in A1) please try:

 =MID(A1,FIND(":",A1)+1,10)  

To find what is in parentheses in 11036541 = the node id (computer-security-cables) :76 = sales ranking (assumed to be in A3) please try:

=MID(A3,FIND("(",A3)+1,FIND(")",A3)-FIND("(",A3)-1)
网友答案:

Try Data / Text to Columns / Delimited / Other / ":" to use ":" as a seperator between the two fields

分享给朋友:
您可能感兴趣的文章:
随机阅读: