Extracting partial data from one cell to put in another

Tabitha W.

I am trying to extract data from one cell and put it in another.

For example:

So I need everything to the right of the hyphen to end up in the Other Column.

Freymish

Hi Tabitha,

What you'll need to do is read the contents of the cell looking for the hyphen and then use the LEFT() or RIGHT() function to grab the part of the string you want. In the example below I am using the FIND() function to identify where in the the string the "-" is located. then I use the LEFT function to pull only those characters that I want. (Notice that I subtract 1 from the Find result so it doesn't include the "-" in the string.)

[Full Name]@row = "Bob-Johnson"

=LEFT([Full Name]@row, FIND("-", [Full Name]@row) - 1)

So first I use FIND() to determine where in "Bob-Johnson" the "-" is. The answer is 4 as it counts the characters from left to right. Then I subtract one so the answer is 3.

Then that answer becomes the num_chars value in the LEFT function:

LEFT( text [ num_chars ])

Going to the right is a little trickier as you need to know the length of the string in total in order to know how many characters you need.

Using the LEN() function tells us the total string lengh is 11 characters. so if I subtract the first 4 I get from the FIND() I'm left with the second half of the string, i.e. "Johnson"

=RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND("-", [Full Name]@row))

Hope that makes sense. :)

Tabitha W.

Thank you very much!

Deanna Copello

I am wanting to copy only the first 50 characters of one cell. I am using this formula to capture the beginning of the cell: =RIGHT([Order Status Comments]@row, LEN([Order Status Comments]@row) - FIND("", [Order Status Comments]@row) + 2)

This works perfectly. However, I want to limit the characters to 50. How would I identify that?