How to split the content of one cell into separate columns

SJTA

I have a cell with data in this format:

Approved,AAD:tammy.couch@transcoretech.com , 9/3/2019 11:06:50 AM

I already have a formula for the Status Column, which is Approved. That formula is =IFERROR(LEFT([MFG Approval (archived)]@row, FIND(",", [MFG Approval (archived)]@row) - 1), ""). It works great.

However, I need to extract the email address for the contacts column and the date for the date column. The email address is between the ":" and the " ,". Please help me with the formulas for the Contacts and Date columns.

KPH

Extracting the email address

1.Find Start Position

First you need to identify the starting position of the email address, which I hope is the only time a colon is used. If so, you can do that using:

=FIND(":", [MFG Approval (archived)]@row)

(just like how you found the comma for your other formula)

Then add one to find the position of the first character after the colon:

=FIND(":", [MFG Approval (archived)]@row)+1

2. Find End Position

Then you need to find the second comma in the string. You can't find the second occurrence of something but you can substitute the second occurrence of something. So we use a SUBSTITUTE function to convert the comma into a unique character (I am using *, if you have * in your data use something else).

=SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)

Then we add a FIND function to find the position of that *:

=FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2))

3. Calculate Length

Then we subtract the start position (position of the colon) and the colon itself (that's the -1), from the end position (position of the second comma) to find the length of the email address:

= FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)) - FIND(":", [MFG Approval (archived)]@row) - 1

4. Extract Email Address

Now we know where the email address starts and how long it is, we can use a MID function to return it.

The syntax is

=MID(text, start position, number of characters)

In other words

=MID([MFG Approval (archived)]@row, formula 1, formula 3)

Or

=MID([MFG Approval (archived)]@row, FIND(":", [MFG Approval (archived)]@row) + 1, FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)) - FIND(":", [MFG Approval (archived)]@row) - 1)

This will include the space that you have at the end of the email address as it is using the comma as the delimiter. You can remove this if you wanted to.

Extracting the Date

You can use the same process to find the date. If you spaces are consistent, you can use the second comma as the start identifier for your date and the third space as the end. If the spaces are not consistent, you can use the forward slashes.

Your end position (formula 2) for the forward slash would be:

= FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, "/", "*", 2)) + 5

This replaces the second forward slash with a star, then finds the position of the star and adds 5 to find the position of the character after the 4-digit year.

Hopefully that all makes sense and you can take it from there. Have fun!

KPH

Extracting the email address

1.Find Start Position

First you need to identify the starting position of the email address, which I hope is the only time a colon is used. If so, you can do that using:

=FIND(":", [MFG Approval (archived)]@row)

(just like how you found the comma for your other formula)

Then add one to find the position of the first character after the colon:

=FIND(":", [MFG Approval (archived)]@row)+1

2. Find End Position

Then you need to find the second comma in the string. You can't find the second occurrence of something but you can substitute the second occurrence of something. So we use a SUBSTITUTE function to convert the comma into a unique character (I am using *, if you have * in your data use something else).

=SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)

Then we add a FIND function to find the position of that *:

=FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2))

3. Calculate Length

Then we subtract the start position (position of the colon) and the colon itself (that's the -1), from the end position (position of the second comma) to find the length of the email address:

= FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)) - FIND(":", [MFG Approval (archived)]@row) - 1

4. Extract Email Address

Now we know where the email address starts and how long it is, we can use a MID function to return it.

The syntax is

=MID(text, start position, number of characters)

In other words

=MID([MFG Approval (archived)]@row, formula 1, formula 3)

Or

=MID([MFG Approval (archived)]@row, FIND(":", [MFG Approval (archived)]@row) + 1, FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2)) - FIND(":", [MFG Approval (archived)]@row) - 1)

This will include the space that you have at the end of the email address as it is using the comma as the delimiter. You can remove this if you wanted to.

Extracting the Date

You can use the same process to find the date. If you spaces are consistent, you can use the second comma as the start identifier for your date and the third space as the end. If the spaces are not consistent, you can use the forward slashes.

Your end position (formula 2) for the forward slash would be:

= FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, "/", "*", 2)) + 5

This replaces the second forward slash with a star, then finds the position of the star and adds 5 to find the position of the character after the 4-digit year.

Hopefully that all makes sense and you can take it from there. Have fun!

SJTA

@KPH Thank you very much!!

I successfully extracted the email address, but struggled with date. Really appreciate your help.

KPH

Did you manage to get the date or are you still stuck? The process is the same - find the start, find the end, use that to calculate the length, and then extract using a MID. If you are stuck, please share examples. What we use to identify the start and end will depend on how uniform the data is. It could be the comma and space, it could be the / within the date.

SJTA

I didn't get the date. I am sure I am not applying your instructions correctly. I was trying to tinker with it as much as possible, but I am sure there is a flaw in my logic and syntax. Here is what I was thinking.

The syntax: =MID(text, start position, number of characters)

=MID([MFG Approval (archived)]@row,

Start position. I am figuring that the start is the second comma, so

FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2) + 2

End position. As you indicated, so

FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, "/", "*", 2)) + 5

Therefore,

=MID([MFG Approval (archived)]@row, FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2) + 2, FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, "/", "*", 2)) + 5) - FIND("*", SUBSTITUTE([MFG Approval (archived)]@row, ",", "*", 2) - 1))

Of course Smartsheet says it's an Incorrect Argument.