truncating text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Scott,

Try this,

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

Where A1 is your text,

Does that help?

Steve
 
Hi,

You could use something like this:

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

Regards,
Bondi
 
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.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find truncated cells 1
Intentionally truncating text characters 2
How do I recover text that was truncated/ deleted in Excel? 1
Truncate a data entry cell 3
Truncating text field in column 2
Macro that will insert a simple text string in top cell of text file 1
formula to truncate text characters 6
Text Truncated after Paste link in Excel 2002 3

Back
Top