Two questions

Is it possible to do the following two things?

1) Count the number of characters (including spaces) in a cell?

2) Find and replace the initial space in cells? I have a document that has over two thousand alpha strings. Many of them have an initial space which I need to remove. Manually, this is a pain.

going to answer both questions in one:

=RIGHT(<a1>,LENB(a1)-1)

copy down

hope this helps(but you probably want to make sure that NONE of your text has a space at the end too).

forgot another caution: make sure that the initial space is actually just 1 space.

I hope someone else has a QA safer way.

Originally Posted by Intelligence3

1) Count the number of characters (including spaces) in a cell?

Use the LEN function...

=LEN(A1)

2) Find and replace the initial space in cells? I have a document that has over two thousand alpha strings. Many of them have an initial space which I need to remove. Manually, this is a pain.

If by 'initial space' you mean the first character in each cell, try...

=RIGHT(A1,LEN(A1)-1)

If by 'initial space' you mean the first instance in each cell, try...

=SUBSTITUTE(A1," ","",1)

Then, select your cell or range of cells and > Edit > Copy > Edit > Paste Special > Values > Ok.

Hope this helps!

Thanks for the replies!

There's also the TRIM worksheet function that removes all "excess" spaces in a text string. If I put " hello everyone " in a cell, then apply the TRIM function, I get "hello everyone" back without the leading or trailing spaces, or the extra spaces between words.