How to remove the rightmost N characters when the string length is variable

**OmniBlue** · 12-17-2015, 04:49 PM

Hello!

I have a column of text values (strings) and would like to remove the rightmost 8 characters from each cell. The string length varies from cell to cell, however, so I don't believe I can use the LEN function. The rightmost 8 characters are in the form (xxxxxx).

Thank you!

**Ron Coderre** · 12-17-2015, 04:51 PM

If a formula is acceptable....

Please Login or Register  to view this content.

(I corrected the formula I originally posted....I saw "8" and typed "9")

Is that something you can work with?

**Pete_UK** · 12-17-2015, 04:51 PM

Try this in B2, assuming the string is in A2:

=LEFT(A2,LEN(A2)-8)

Copy down as required.

Hope this helps.

Pete

**JieJenn** · 12-17-2015, 04:52 PM

Replace A1 with your cell reference
=IFERROR(LEFT(A1,LEN(A1)-8),A1)

**OmniBlue** · 12-17-2015, 05:00 PM

Thank you, everyone! For some reason, when I enter the formula, I'm seeing the formula itself--rather than the result of the formula. Any idea why that may be? I did go to File --> Options --> Formulas --> Workbook Calculation, and it is set to automatic.

**OmniBlue** · 12-17-2015, 05:02 PM

Also, both the source column and the destination column are formatted to Text (Select cells --> Format Cells--> Text).

**Ron Coderre** · 12-17-2015, 05:05 PM

Either
1) The cell containing the formula is formatted for TEXT.
...Change the number format to anything except text
...Edit the formula and press ENTER

or
2) The worksheet is set to display formulas, not their calculated values
...CTRL+~ <--the "~" is on the key above the TAB key
or
...Formulas.Show_Formulas

Does that help?

**OmniBlue** · 12-17-2015, 05:14 PM

Thank you, Ron! Your advice resolved the problem, and the formula you provided worked as needed!

How to remove the rightmost N characters when the string length is variable

LinkBack

Thread Tools

Rate This Thread

Display

How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Re: How to remove the rightmost N characters when the string length is variable

Thread Information

Users Browsing this Thread

Similar Threads

Cell length formula to remove any characters over 31 in length

[SOLVED] VBA, remove all characters within a string, except numerical characters and full stops

[SOLVED] Shorten string length if exceeds 31 characters (max sheet name length)

Remove text of variable length from cell

Passing Variable length Space Characters to Variables

want to remove all text characters equal to one character in length from text string

want to remove all text characters equal to one character in length from text string

Bookmarks

Bookmarks

Posting Permissions