How to trim spaces from entered text in cell using exisiting functions?

  • Thread starter Thread starter rj
  • Start date Start date
R

rj

I want to enter a letter in a cell then trim any spaces from the text and
convert it to lower case.
Can I do this by using functions in the cell where I enter the text? I'm
developing a spreadsheet to be used by Pocket Excel so I need to use
exisiting functions.

For example if I enter a letter in A1 I've tried the formula LOWER(A1) in A1
but I can't do this due to a circular reference error.

thanks
rj
 
You will need to use a "helper" column to do any changes and then copy/paste
special=> values to either the original column or the helper column; then
delete column not required.
 
rj wrote...
I want to enter a letter in a cell then trim any spaces from the text and
convert it to lower case.
Can I do this by using functions in the cell where I enter the text? I'm
developing a spreadsheet to be used by Pocket Excel so I need to use
exisiting functions.
....

If you mean you want to find some way to convert entries automatically,
there's no way to do that without macros. If Pocket Excel supports
macros, then you could use a Change event handler. If it doesn't
support macros, you simply can't do this. However, you could *enter*
into one cell [B5, say] and cleanse the entry in another cell [BB5,
say, containing the formula =LOWER(TRIM(B5))], then use the cleansed
cell [BB5] in other formulas.
 
If you mean you want to find some way to convert entries automatically,
there's no way to do that without macros. If Pocket Excel supports
macros, then you could use a Change event handler. If it doesn't
support macros, you simply can't do this. However, you could *enter*
into one cell [B5, say] and cleanse the entry in another cell [BB5,
say, containing the formula =LOWER(TRIM(B5))], then use the cleansed
cell [BB5] in other formulas.


Pocket Excel is a very trimmed down version and does not support anything
beyond pretty basic functions. I have a pocket pc mainly for finding my way
using a Bluetooth connected gps, I have never found anything interesting in
Pocket Excel except testing it's limitations after reading postings about it
that I can't do with my cell phone. I think it's too small to do anything
really useful, entering anything on a pocket pc with that little pin is a
real hassle


Peo
 
Hi, thanks for the response.

Yes, I do want to convert the entries automatically. I don't beleive Pocket
Excel supports Macros.
So, the only way it seems to work is to do as you suggested and use an
additional dummy cell to alter the original entry and then use the dummy
cell in other forumulas.

Thanks
rj

Harlan Grove said:
rj wrote...
I want to enter a letter in a cell then trim any spaces from the text and
convert it to lower case.
Can I do this by using functions in the cell where I enter the text? I'm
developing a spreadsheet to be used by Pocket Excel so I need to use
exisiting functions.
...

If you mean you want to find some way to convert entries automatically,
there's no way to do that without macros. If Pocket Excel supports
macros, then you could use a Change event handler. If it doesn't
support macros, you simply can't do this. However, you could *enter*
into one cell [B5, say] and cleanse the entry in another cell [BB5,
say, containing the formula =LOWER(TRIM(B5))], then use the cleansed
cell [BB5] in other formulas.
 
Back
Top