Hey,
I have a lot of data that looks like this:
1979,410,1,0,"S2","0",1,,,1431939840,0
1984,76,2,0,"S2","0",1,,,8667779072,0
2003,76,2,0,"S2","11",1,,,46718346,0
I want to extract the numbers after the ",,," but NOT include the last ",0" (for example 46718346 for 2003). I have tried to google an answer for some time now, but can't find an appropriate one so would really appreciate some help!
Thanks!
Hi and welcome to the forum
Assuming yoyr data is in A1, try this, copied down...
=MID(A1,SEARCH(",,,",A1,1)+3,SEARCH("xx",SUBSTITUTE(A1,",","xx",10),1)-SEARCH("xx",SUBSTITUTE(A1,",,,","xx",1),1)-3)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks, but I just get the #VALUE in return from Excel... I can copy paste some of my data in here:
1983,76,1,0,"S2","0",1,,,1342739328,0
1983,76,2,0,"S2","0",1,,,7436109824,0
1983,152,1,0,"S2","0",1,,,406302528,0
1983,152,2,0,"S2","0",1,,,702199424,0
1983,410,1,0,"S2","0",1,,,1711859840,0
and so on... It's the bold numbers I want.
This is different values of exports and imports from different countries over time, so there isn't any direct patterns in the data. Except the ",,," and the ending ",0". So it's (almost) always different lengths of the numbers in the cells aswell...
try
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-2),",",REPT(" ",25)),20))
or
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-2),",,,",REPT(" ",25)),20))
or
=SUBSTITUTE(MID(A1,FIND(",,,",A1)+3,255),",0","")
Last edited by martindwilson; 10-17-2013 at 06:22 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I used that on your sample data...
1979,410,1,0,"S2","0",1,,,1431939840,0 1431939840 1984,76,2,0,"S2","0",1,,,8667779072,0 8667779072 2003,76,2,0,"S2","11",1,,,46718346,0 46718346
=MID(A26,SEARCH(",,,",A26,1)+3,SEARCH("xx",SUBSTITUTE(A26,",","xx",10),1)-SEARCH("xx",SUBSTITUTE(A26,",,,","xx",1),1)-3)
But Martin's is shorter and easier
try this
=IFERROR(SUBSTITUTE(MID(A1,FIND(",,,",A1)+4,LEN(A1)),",0",),"")
... Or try to replace *,,, with nothing.
Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks