truncating text

[scott_cairns]

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!

[SteveG]

Scott,

Try this,

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

Where A1 is your text,

Does that help?

Steve

[Bondi]

Hi,

You could use something like this:

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

Regards,
Bondi

[Dave O]

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.

[scott_cairns]

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

[SteveG]

You're welcome.

Steve

[Anoop George]

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

[SteveG]

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