software

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 quot;The Religions of Indiaquot;, I would B1 to read
quot;thereligionsofinquot; ... 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!


Scott,

Try this,

=LOWER(MID(SUBSTITUTE(A1,quot; quot;,),1,16))

Where A1 is your text,

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=540663Hi,

You could use something like this:

=TRIM(LOWER(SUBSTITUTE(quot;The Religions Of Indiaquot;,quot; quot;,quot;quot;)))

Regards,
BondiAbsolutely possible: with your title in cell A1, for instance,
=MID(LOWER(SUBSTITUTE(A1,quot; quot;,quot;quot;)),1,16)

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

quot;SteveGquot; wrote:

gt;
gt; Scott,
gt;
gt; Try this,
gt;
gt; =LOWER(MID(SUBSTITUTE(A1,quot; quot;,),1,16))
gt;
gt; Where A1 is your text,
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=540663
gt;
gt;


You're welcome.

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=540663Please try this this is more simplified

=LOWER(SUBSTITUTE(A1,quot; quot;,quot;quot;))

Thanks
Anoop
quot;SteveGquot; wrote:

gt;
gt; Scott,
gt;
gt; Try this,
gt;
gt; =LOWER(MID(SUBSTITUTE(A1,quot; quot;,),1,16))
gt;
gt; Where A1 is your text,
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=540663
gt;
gt;


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: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=540663