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.
 
Please try this this is more simplified

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

Thanks
Anoop
 
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
 
You must log in or register to reply here.

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
Truncate a data entry cell 3
How do I recover text that was truncated/ deleted in Excel? 1
Truncating text field in column 2
Macro that will insert a simple text string in top cell of text file 1
Why is Excel truncating my text to 255 characters? 2
formula to truncate text characters 6

Back
Top