I need to separate out digits between characters (_) into a different column. Can this be done?

Sherry Wolhaupter

Hi there …

I need to separate digits from between characters (_) into a separate column.

100936_BLK_IE_JUL_20240718231356_24_HI_REGULAR_4.ps

100936_BLK_IE_JUL_240718231626_6_HI_REGULAR_6.ps

I need returned 24 and 6 from the strings above. Since there are multiple _ I'm not sure how to tell the formula to pull the digits between the 5th and 6th _. Any help is greatly appreciated.

Thank you

Kerry St. Thomas

=VALUE(SUBSTITUTE(MID([File Name]@row, FIND("|", SUBSTITUTE([File Name]@row, "_", "|", 5)) + 1, 2), "_", ""))

Working from the middle of the formula outward:
SUBSTITUTE = replaces the fifth occurrence of an underscore _ with a pipe | —basically, load a NEW character that's wildly unlikely to be repeated elsewhere in the string. (If this character is part of your naming convention, change the character in the entire formula
FIND = find that pipe | (or whatever your character would be)
MID = find return the two characters following the pipe | or whatever
SUBSTITUTE = if there's an underscore in the result (like there would be with 6_), get rid of it
VALUE = convert it to a number format (for calculations elsewhere)

I assumed you are only trying to pull two characters; if you are pulling a different number of characters you'll need to make adjustments.

Good luck!

Kerry St. Thomas

=VALUE(SUBSTITUTE(MID([File Name]@row, FIND("|", SUBSTITUTE([File Name]@row, "_", "|", 5)) + 1, 2), "_", ""))

Working from the middle of the formula outward:
SUBSTITUTE = replaces the fifth occurrence of an underscore _ with a pipe | —basically, load a NEW character that's wildly unlikely to be repeated elsewhere in the string. (If this character is part of your naming convention, change the character in the entire formula
FIND = find that pipe | (or whatever your character would be)
MID = find return the two characters following the pipe | or whatever
SUBSTITUTE = if there's an underscore in the result (like there would be with 6_), get rid of it
VALUE = convert it to a number format (for calculations elsewhere)

I assumed you are only trying to pull two characters; if you are pulling a different number of characters you'll need to make adjustments.

Good luck!

Sherry Wolhaupter

Thank you Kerry! I will try this and post if I can get it to work.

Sherry Wolhaupter

Unfortunately, I could not get this to work. Any other suggestions?

Sherry Wolhaupter

I got it to work. Thank you!