text to columns

TimMelton

Hello. I'm looking to split out the following string to provide just the supervisor name and I've yet to figure out the formula.. thoughts on how I do that in a helper column? Thanks

Supervisor | Approved by Little, Paul on 12/4/23 @ 11:42 AM |

Paul Newcome

FIND outputs a number based on where specific text is found within a string.

Using your example in the original post,

FIND("by", [Column Name]@row)

will output the number 23. Adding 3 to that gives us 26. We use 26 as the starting point in the MID function. So technically it could be written as

=MID([Column Name]@row, 23 + 3, 38 - (23 + 3))

=MID([Column Name]@row, 26, 38 - 26)

=MID([Column Name]@row, 26, 12)

But hardcoding numbers in is very rigid and does not allow for any flexibility at all. So we use the FIND function to dynamically output those two numbers for the MID function so that if there are additional characters that come before the "by" or the name isn't exactly 12 characters long, it will still work as long as the name is preceded by "by" and followed by "on".

Paul Newcome

will the supervisor's name always follow "Approved by" and be followed by "on"? If so, you can use something along the lines of

=MID([Column Name]@row, FIND("by", [Column Name]@row) + 3, FIND("on", [Column Name]@row) - (FIND("by", [Column Name]@row) + 3))

TimMelton

ah, thanks @Paul Newcome , yes, it will always follow the approved by. What does the +3 do in the formula? I assuming it may need to be adjusted if the string changes?

TimMelton

I think I figured it out, that adjusts the position of the return, meaning it finds the string "on" or "by" and returns the data +3 characters.. is that correct?

Paul Newcome

FIND outputs a number based on where specific text is found within a string.

Using your example in the original post,

FIND("by", [Column Name]@row)

will output the number 23. Adding 3 to that gives us 26. We use 26 as the starting point in the MID function. So technically it could be written as

=MID([Column Name]@row, 23 + 3, 38 - (23 + 3))

=MID([Column Name]@row, 26, 38 - 26)

=MID([Column Name]@row, 26, 12)

But hardcoding numbers in is very rigid and does not allow for any flexibility at all. So we use the FIND function to dynamically output those two numbers for the MID function so that if there are additional characters that come before the "by" or the name isn't exactly 12 characters long, it will still work as long as the name is preceded by "by" and followed by "on".

TimMelton

Thanks for the detailed summary, very much appreciated. It worked perfectly.

Paul Newcome

Happy to help. 👍️