Removing spaces in text

J

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
 
N

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
|
|
 
H

Harlan Grove

Jeff Granger said:
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.
Click to expand...

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.
 
Top