how to get excell to locate certain characters

[yoniratner]

hi.

i have a long list of what used to be file names that look like this:
2013.6.26,MAGAMA 219641,ZANUAH,QD(2.48),SSD(2.58),WA(3.4)-OK.pdf

each segment of that line has meaning.
what i need is a command that i can put in a box on the spreadsheet that tells it to scan this line, find, for example, the letters QD, and return the number in the prentices next to those letters.

can this even be done?

yours,
yoni

[XOR LX]

Hi,

I'm afaid you're going to have to be a bit more specific: "find, for example, the letters QD,..." does not tell us what the other 'examples' of strings to find are.

Could your problem perhaps be rephrased? For example, is it the case that every string in parentheses is to be returned (into separate cells)? Or are there also strings within parentheses that are not to be returned?

Either way, you'll need to provide more information in order for a meaningful solution to be reached.

Regards

[Andy Pope]

assuming text in A1 and example letters to find in B1 then

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(B1,A1),LEN(A1)),B1&"(",""),")",REPT(" ",255)),255))*1

[yoniratner]

well, the end result im trying to create is a table where one column will be composd entirely of lines like te one i pasted, while the other columns will be QD, SSD, WA and other letter combinations that exist in other lines. each such letter combinaton always has parentheses next to it. the idea is to make the values in the parentheses appear in the box which is intersected by the row the line is in and the column that represents that letter combination.

something like the attacjed file (i hope the attachment was succesful).

what im trying to do is make the columns "find" the letter combinations on their own inside the line of text and automatically copy the value in the parentheses next to that letter combination into the box.

regards
yoni

[XOR LX]

Then just use Andy's solution but amended for your ranges, e.g. in B2 and copy across and down:

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

Note that this will fail in cell E3 as your desired return (NC,136) is text. Removing the *1 at the end of the above formula will rectify this, though will also have the drawback that all your results are now textual, rather than numeric.

Regards

[yoniratner]

works great

thanks

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.