hello

i would like to create a cell that truncates the titles of books to 16
characters and no spaces.

so for instance if A1 reads "The Religions of India", I would B1 to read
"thereligionsofin" ... no uppercase and no spaces.

I realise that excel may not be the best place to manipulate data like this,
but is it possible? thanks!

Hi,

You could use something like this:

=TRIM(LOWER(SUBSTITUTE("The Religions Of India"," ","")))

Regards,
Bondi

Scott,

Try this,

=LOWER(MID(SUBSTITUTE(A1," ",),1,16))

Where A1 is your text,

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540663

works perfect! thanks!

"SteveG" wrote:


Scott,

Try this,

=LOWER(MID(SUBSTITUTE(A1," ",),1,16))

Where A1 is your text,

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540663

You're welcome.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540663

Please try this this is more simplified

=LOWER(SUBSTITUTE(A1," ",""))

Thanks
Anoop
"SteveG" wrote:


Scott,

Try this,

=LOWER(MID(SUBSTITUTE(A1," ",),1,16))

Where A1 is your text,

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540663

Anoop,

The OP asks to return only the first 16 characters of the text, your
post will return all characters so although it is a simpler formula, it
won't produce the desired results. Adding the MID function allows the
formula to return a specified number of characters.


Regards,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540663

Absolutely possible: with your title in cell A1, for instance,
=MID(LOWER(SUBSTITUTE(A1," ","")),1,16)

SUBSTITUTE removes all the blanks, LOWER converts to lower case, and
MID returns the first 16 characters.