Link to home
Start Free TrialLog in
Avatar of crepe
crepeFlag for United States of America

asked on

Returning numbers and text based on file name

Good morning,

I have a column of data containing file names that I need to extract data from based on certain conditions.

Column A
A2_001.002.003.004.005B_001
A2_001.002.003.004.005B_002
A2_001A.002.003.004.005B_001
A2_001A.002.003.004.005B_003


Conditions
1. If the last number of the file name is 1 (001 at the end of the file name above), then I need to return the first group of numbers after the "_". So it'll be "001".
2. If the last number of the file name is 2 (002 at the end of the file name above), then I need to return the second set of numbers after the "." so it'll be "002".
3. The same goes for 003, 004 and 005 in the same manner.

I can figure the above correctly with formulas but I can't seem to figure out how to return the next conditions.

4. I need to return the letters "A" and "B" above which can follow any of the sets of numbers in Column C. If there is only one letter, then it is linked with all the groups prior to the letter.

So Column B and C will return the following:
001    B
002    B
001    A
003    B

How do I do this? Thank you!!
Avatar of felixdsouza
felixdsouza
Flag of India image

Are you ok with using a macro to accomplish this ?
Avatar of crepe

ASKER

Yes!
ASKER CERTIFIED SOLUTION
Avatar of felixdsouza
felixdsouza
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crepe

ASKER

It's perfect. Thank you!