• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Value from a Parameter in a String

D

dotcomken

New Member
I have been struggling all day to find a function to help me get the value of parameter within a long string of text.


An example of the string:


QTY1=1&CID=555666&AMT3=10.79&AMT2=267.29&AMT1=379.99&TYPE=352176&ITEM3=02211000000&ITEM2=02217109000&ITEM1=07180858000&CURRENCY=USD&OID=367018635&QTY3=1&QTY2=1


The total list of variables is around 50 with about 6000 rows. I only need to extract a few of these variables so what I was hoping to do was in each column look for one of the variables in the string and print the value.


STRING | CID | QTY1 | ITEM1 | AMT1 | ect..


Without using macros is there a solution that I could drop the variable into and print the value?


Thanks in advance for your help.
 
Hi, dotcomken!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


And about your question...


Give a look at this file:

https://dl.dropbox.com/u/60558749/Extract%20Value%20from%20a%20Parameter%20in%20a%20String%20%28for%20dotcomken%20at%20chandoo.org%29.xlsx


It extracts every parameter in the string, then you'll only have to hide the unneeded columns.

For each param it uses 4 columns: two for the name (position and name) and two for the value (position and value). Just adjust the number of parameters copying columns F:I to the right as necessary. First 4 columns (B:E) are dummy columns just for make easier and shorter F:I formulas.


Please advise if any issue.


Regards!


EDIT: Please note that an additional group of 4 columns is needed for nth+1 parameter.
 
Dotcomken


Firstly, Welcome to the Chandoo.org forums.


Assuming your strings are in Column A starting in A2

For CID in B2: =MID($A2,FIND("CID=",$A2)+4,FIND("&AMT",$A2)-FIND("CID=",$A2)-4)

For Qty1 in C2: =MID($A2,FIND("QTY1=",$A2)+5,FIND("&CID",$A2)-FIND("QTY1=",$A2)-5)

For Item1 in D2: =MID($A2,FIND("ITEM1=",$A2)+6,FIND("&CURRENCY",$A2)-FIND("ITEM1=",$A2)-6)

For Amt1 in E2: =MID($A2,FIND("AMT1=",$A2)+5,FIND("&TYPE",$A2)-FIND("AMT1=",$A2)-5)


The above are all strings and if you want values put a value around them eg: =Value( above formula)


You should be able to work out how to extract Items 2, 3 etc
 
Hi ,


One more method to add to the others already posted :


1. Once all the 6000 rows have been imported into Excel in say Sheet1 , use the Text to Columns facility to split all the text into columns , using the & sign as a delimiter.


This will leave you with :


CID=555666

AMT3=10.79


and so on in columns A , B , C ,...


2. On a different sheet , use the following formulae :


=RIGHT(Sheet1!B1,LEN(Sheet1!B1)-FIND("=",Sheet1!B1))


Copy this across and down.


This will leave all the parameter values in the various columns. Use the parameters as the headers.


Narayan
 
I also figured if Dotcomken was the real Ken DotCom (ala Megaupload) he could afford someone to write a formula for him?
 
I realise that he isn't Ken Dotcom but Well maybe this Journalist doesn't know the difference ?

http://advanced-television.com/index.php/2012/05/31/ken-dotcom-seeks-case-dismissal/
 
Thanks for your replies. I am the @dotcomken from twitter but that's about it.


Sorry if I posted something out of context. I had been searching for at least 2hrs on Google and on here. All of the solutions that I found were for something similar but did not work for all variables.


The closest thing I could find was: LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",FIND("AMT1=",A2))),ROW($1:$10000))))


But it would not work to retrieve pricing or anything that wasn't numbers. I appreciate all your help.
 
One more formula, but more versatile. Assumes long string is in col A, labels of desired items are in row 1, starting in col B.

=TRIM(LEFT(SUBSTITUTE(MID($A2,FIND(B$1,$A2)+LEN(B$1)+1,999),"&",REPT(" ",999),1),999))

Copy down and to the right as desired.


Small note, these formulas all return text strings, so any numbers you see are actually numbers stored as text. This might cause some problems down the line, so wanted to give a warning.
 
Dotcomken


Didn't my solution, Post 3 above, do what you want ?


Can you please clarify your requirements if it didn't?
 
I tried a few of the solution you all have suggested. I am running into a problem with the parameters not being in the same order on the string.


I uploaded a small sample to dropbox: https://www.dropbox.com/s/4eme9wfch3xgpmf/SortingStringsByParam.xlsx


I also tried to distribute text to columns. However, sorting by rows from the largest string did not properly sort them on the rest of the rows. It didnt seem to make any sense
 
Hi, dotcomken!

At least in my example all the parameter names & lengthts are variable and delimited only by "&" and "=". Any issue arised?

Regards!
 
CID: =IFERROR(MID($A2,FIND("CID=",$A2)+4,FIND("&",$A2,FIND("CID=",$A2)+1)-FIND("CID=",$A2)-4),"not found")


Qty: =IFERROR(MID($A2,FIND("QTY1=",$A2)+5,FIND("&",$A2,FIND("QTY1=",$A2)+1)-FIND("QTY1=",$A2)-5),"Not found")


Item: =IFERROR(MID($A2,FIND("ITEM1=",$A2)+6,FIND("&",$A2,FIND("ITEM1=",$A2))-FIND("ITEM1=",$A2)-6),"Not found")


Amt: =IFERROR(MID($A2,FIND("AMT1=",$A2)+5,FIND("&",A2,FIND("AMT1=",$A2)+1)-FIND("AMT1=",$A2)-5),"Not found")


The above are more generic now and will adjust by changing all occurrences for say AMT1 to AMT7 etc
 
Hi, dotcomken!


Here's the updated file with the examples you uploaded. Please check it and advise.

https://dl.dropbox.com/u/60558749/Extract%20Value%20from%20a%20Parameter%20in%20a%20String%20%28for%20dotcomken%20at%20chandoo.org%29.xlsx


Regards!


EDIT: In second sheet you can enter the desired parameters to be extracted... ordered by columns.
 
@SirJB7 - Thanks for the updated file. It was working until I hit a resources available wall and it crashed and corrupted on me.


@Hui - Thanks for the formulas I was able to use them to grab some data. I only had to remove a chars from a few of the values that contained a = or another char. I also found them not finding the parameters when they did exist. Not sure what the issue. The data was very messy so im sure there was something throwing the formulas off at times.


Thanks Again!
 
Hi, dotcomken!

I don't understand what you meant. Is that an Excel error? I've never read such one.

Could you please upload the related file, if no security issues?

Regards!
 
There was a small error in Item:

Refer below:


Item: =IFERROR(MID($A2,FIND("ITEM1=",$A2)+6,FIND("&",$A2,FIND("ITEM1=",$A2)+1)-FIND("ITEM1=",$A2)-6),"Not found")
 
You must log in or register to reply here.
Back
Top