Help With Trim Function

[Roff]

Hello -

In example below "_" should be read a just a single space " "

Database returns Name of Account as follows:

A1: "Account_Name__LLC____"

What I would like to do is remove the spaces after the "LLC" without removing the double spaces between "Account Name" and "LLC"

=TRIM(A1) returns "Account_Name_LLC" but need "Account_Name__LLC" (keeping double spacing between "Name" & "LLC")

Using Excel 2007.

Thanks for the help!

 

[Jonmo1]

Welcome to the board..

Is there only 2 sets of double spaces..
1 between acount name and llc
2 at the end
?

Try

=SUBSTITUTE(A1," ","",2)

 

[Roff]

The number of spaces after "LLC" is variable.

Could be "Account_Name__LLC___" or "Account_Name__LLC_________"

Would just like to remove spaces at end, but preserve spacing between text.

 

[Jonmo1]

Do ALL account names end with LLC ?
probably not, but just asking.

 

[Rick Rothstein]

You can use this normally entered formula...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"")))))

 

[Roff]

@jonmo1 - account ending is also variable, LLC was just example

@Rick - that formula works (though still haven't figured out how), thanks!

 

[Rick Rothstein]

You can use this normally entered formula...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"")))))

@Rick - that formula works (though still haven't figured out how), thanks!

Great, I am glad it worked for you. Here is roughly how it works. This part...

LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),""))

finds the last non-space character, that is what RIGHT(TRIM(A1),1) does, and then counts how many of those characters there are. This will be used in another SUBSTITUTE function call as the instance number. Next, look at this part of the formula...

SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"")))

The part I marked in red is the instance number we calculated above. So what is going on here is we are replacing the last non-space character in the original text with a pipe symbol. We can do that because no matter how many times it occurs in the text, we know its instance number. Okay, now lets look at the whole formula...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"")))))

The part I marked in red is the original text with the last non-space character replaced with a pipe symbol. We use the FIND function to locate it and use that location in the LEFT function call against the orignal text to pull out all characters up to and including that last non-space character. That is it... hopefully my description helped you to better understand the formula.

One caution I forgot to mention in my first post which might be obvious to you now... the formula won't work if the original text contains pipe symbols within it. The fix, of course, is easy... just use some other character that you know won't ever be in the text in place of the pipe symbols in the formula. If, however, your text is such that any character can appear in it (so you don't know what character to use in place of the pipe symbol), then here is a generic form of my formula that will work no matter what characters are used in the text. The way I do that is to use a non-typable character in place of the pipe symbol... I use the character whose ASCII code is 1 for that.

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,RIGHT(TRIM(A1)),CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),"")))))

Oh, while you did not ask for it, here is a formula that will remove any spaces only from the front of the text (a LEFT TRIM if you will)...

=MID(A1,FIND(LEFT(TRIM(A1)),A1),LEN(A1))

This formula is also normally entered.