Removing character using Trim..

**Jocote46** · 05-12-2009, 05:01 PM

I need your guys expertise in the following formula. I'm applying the following to a large range of data that varies in lenght,however all of the data has a 1Y or 2Y at the end of it. I need to remove it from the data into a new column.

Currently I'm using =trim(mid(A2,1,30) how can I change my formula to obtain my results?

for example: Nationwide Select premium $74.99 1Y
Nationwide Select Premium W/e-mail $74.99 1Y

As you can see the lenght of the data is different. Thanks in advance for your help.

**martindwilson** · 05-12-2009, 05:03 PM

=LEFT((A1),LEN(TRIM(A1))-2)
note the trim only removes spaces. so

=left(a1,len(a1)-3)) would probably work as tong as there is a space between end of string and 1y

**Chance2** · 05-12-2009, 07:52 PM

How about using a nested SUSTITUTE?

=SUBSTITUTE(SUBSTITUTE(A2," 2Y","")," 1Y","")

It assumes there is a space before the 2Y or 1Y and that they only occur once in the string.

**zbor** · 05-13-2009, 02:39 AM

How about select -> data -> text to columns -> Delimited -> check By space -> Finish

**Little Master** · 05-13-2009, 05:56 AM

How about just replacing 2y & 1y with blank ??

**Jocote46** · 05-13-2009, 01:57 PM

Thank you so much guys, Martin your formula worked great. Thank to the rest for your suggestions. you guys Rock!!

Removing character using Trim..

LinkBack

Thread Tools

Rate This Thread

Display

Removing character using Trim..

Re: Removing character using Trim..

Re: Removing character using Trim..

Re: Removing character using Trim..

Re: Removing character using Trim..

Re: Removing character using Trim..

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions