I have values + spaces in cell A1. I would like cell A2 to display all the
values EXCEPT the last 9. the number of characters in cell A1 can vary.

I know how to use the =RIGHT formula to show the last 9, but I don't know
how to use it to show all but the last 9.

For example, cell A1 = Orange B Apple 123. I would like A2 to = Orange B.

The # of characters and spacing will vary.

Thanks!

1. In cell A2, type the following formula:
   Formula:

   =LEFT(A1,LEN(A1)-9) 

2. Press Enter.

The LEFT function extracts a specified number of characters from the beginning of a text string, while the LEN function returns the length of a text string. By subtracting 9 from the length of the text string in cell A1, we get the number of characters to extract from the beginning of the string.

In the example you provided, the formula will return "Orange B" in cell A2. This formula will work for any text string in cell A1, regardless of the number of characters or spacing.

1. In cell A2, enter the following formula:
   Formula:

   =LEFT(A1,LEN(A1)-9) 

2. Press Enter to apply the formula.

The LEFT function takes two arguments: the first is the cell you want to extract characters from (in this case, A1), and the second is the number of characters you want to extract. We use the LEN function to determine the length of the cell contents in A1, and then subtract 9 from that value to exclude the last 9 characters.

In the example you provided, the formula would look like this: This would return "Orange B" in cell A2.

I figured it out myself for once!

=LEFT(a1,LEN(a1)-9)

"Ben" wrote:

I have values + spaces in cell A1. I would like cell A2 to display all the
values EXCEPT the last 9. the number of characters in cell A1 can vary.

I know how to use the =RIGHT formula to show the last 9, but I don't know
how to use it to show all but the last 9.

For example, cell A1 = Orange B Apple 123. I would like A2 to = Orange B.

The # of characters and spacing will vary.

Thanks!

Try

=LEFT(A1,(LEN(A1)-9))

Mike

"Ben" wrote:

I have values + spaces in cell A1. I would like cell A2 to display all the
values EXCEPT the last 9. the number of characters in cell A1 can vary.

I know how to use the =RIGHT formula to show the last 9, but I don't know
how to use it to show all but the last 9.

For example, cell A1 = Orange B Apple 123. I would like A2 to = Orange B.

The # of characters and spacing will vary.

Thanks!

That's great Ben, but be aware that with your sample data you will also
return an invisible trailing space after the "Orange B "

Vaya con Dios,
Chuck, CABGx3



"Ben" wrote:

I figured it out myself for once!

=LEFT(a1,LEN(a1)-9)

"Ben" wrote:

I have values + spaces in cell A1. I would like cell A2 to display all the
values EXCEPT the last 9. the number of characters in cell A1 can vary.

I know how to use the =RIGHT formula to show the last 9, but I don't know
how to use it to show all but the last 9.

For example, cell A1 = Orange B Apple 123. I would like A2 to = Orange B.

The # of characters and spacing will vary.

Thanks!

Sorry, forgot to mention one way to get rid of that trailing space would
be......

=TRIM(LEFT(A1,LEN(A1)-9))

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

That's great Ben, but be aware that with your sample data you will also
return an invisible trailing space after the "Orange B "

Vaya con Dios,
Chuck, CABGx3



"Ben" wrote:

I figured it out myself for once!

=LEFT(a1,LEN(a1)-9)

"Ben" wrote:

I have values + spaces in cell A1. I would like cell A2 to display all the
values EXCEPT the last 9. the number of characters in cell A1 can vary.

I know how to use the =RIGHT formula to show the last 9, but I don't know
how to use it to show all but the last 9.

For example, cell A1 = Orange B Apple 123. I would like A2 to = Orange B.

The # of characters and spacing will vary.

Thanks!