crepe

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!!

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_00

A2_001.002.003.004.005B_00

A2_001A.002.003.004.005B_0

A2_001A.002.003.004.005B_0

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!!

Are you ok with using a macro to accomplish this ?

ASKER

Yes!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

It's perfect. Thank you!