Removing spaces in text

[Jeff Granger]

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

 

[Peo Sjoblom]

One way, with the name in A1

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

 

[Niek Otten]

Hi Jeff,

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

--
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
| SMITHAND.
|
| Any suggestions?
|
| Jeff
|
|

 

[Jeff Granger]

Thanks, both.

Much appreciated

Jeff

 

[Harlan Grove]

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.