Removing spaces in text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Granger - 12 Apr 2007 17:00 GMT

I need to create an 8 letter Account Name from company names, and I need to
remove any spaces in the company name, such that Smith And Wesson Ltd gives
SMITHAND.

Any suggestions?

Jeff

Reply to this Message

Peo Sjoblom - 12 Apr 2007 17:08 GMT

One way, with the name in A1

=LEFT(SUBSTITUTE(A1," ",""),8)

Signature

Regards,

Peo Sjoblom

>I need to create an 8 letter Account Name from company names, and I need to
>remove any spaces in the company name, such that Smith And Wesson Ltd gives
[quoted text clipped - 3 lines]
>
> Jeff

Reply to this Message

Niek Otten - 12 Apr 2007 17:12 GMT

Hi Jeff,

=UPPER(LEFT(SUBSTITUTE(A1," ",""),8))

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need to create an 8 letter Account Name from company names, and I need to
| remove any spaces in the company name, such that Smith And Wesson Ltd gives
[quoted text clipped - 3 lines]
|
| Jeff

Reply to this Message

Jeff Granger - 12 Apr 2007 17:28 GMT

Thanks, both.

Much appreciated

Jeff

Reply to this Message

Harlan Grove - 12 Apr 2007 20:11 GMT

"Jeff Granger" <jeff.gran...@btclick.com> wrote...

>I need to create an 8 letter Account Name from company names, and I
>need to remove any spaces in the company name, such that Smith And
>Wesson Ltd gives SMITHAND.

You'd be far better off assigning arbitrary distinct 8-digit numbers
as account IDs rather than using the first 8 nonspace characters. How
would you distinguish between Smith and Wesson and Smith and Hawken?

Trying to condense a name field into an ID field is a classic database
mistake. You've been warned.

Reply to this Message

Rate this thread: