copying date from text.

[yoniratner]

hello

i have a long list in excel 2007 of what used to be file names that look like this:

2013.6.26,MAGAMA 219642,ZANUAH,PSD(NC,136),UCS(26.7),PL(2.9)-OK.pdf

i previously used this forum to extract the data from these file names, using variations of this formula:

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

this worked wonderfully but now i need some more help.

what i need to do now is to:
1. take just the date at the beginning of the text and put it in a seperate box.
2. take just the first word after the date and put that in a seperate box.
3. take just the number after the word in (2) and put it in a seperate box.
4. take the word right after the number in (3) and put it in another box.
5. when it takes the data from the parenthases after PSD, i need it to put the NC (could be either C or NC) in one box and the number in another box.
6. take the OK at the end and put it in a box (could be OK, ER or UC).

thanks in advance
yoni

[L-Drr]

Please ginve some more information on the text strings:
- are the commas and spaces etc the same in every string?
- are the lengths of the parts of the string the same in every string (e.g. are the variations of the "MAGAMA" part always 6 characters)?

[XOR LX]

Agreed with L-Drr. Also, this type of question has a much better chance of being correctly answered if it is accompanied by not only a clear explanation of the format of the strings involved but also with as many examples (with desired results in each case) as possible.

Giving just one example, unless it is perfectly representative of all strings to be examined, is likely to result in at best many more questions and at worst incorrect answers.

Regards

[yoniratner]

the dates and numbers are all in the same format and length,the commas and spaces are all the same. the length of the words, like MAGAMA or ZANUAH do very.

attached is an example i made manually.

[L-Drr]

lol its weird working from right to left but ill see what i can do

[L-Drr]

ehm... are you sure the dates are all the same length? so no dates in october, november or december (2 digits)?

[yoniratner]

yeah tell me about it, hebrew makes all office programs a hassel.

[yoniratner]

L-Drr: it was originally a mistake made by the people who named the files, but luckaly there are no lab reports from october so we're good. in the future this may change, but if you guys can help me now then when re-formated entries come in im sure ill find a way to adjust the command myself.

[L-Drr]

Here you are.
Be careful: if 1 of the codes like WA or QD is also in a word like MAGAMA, you'll get wrong results. but I guess this is not the case

[L-Drr]

sorry 1 mistake with the NC/C, hold up

[L-Drr]

fixed. in your example you didnt split up the "NC,136", like you said in your first post, so i also didn't do that. if you still want to, please tell me