Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a formula I can use that will change all cells with one character to something else?

For example, I have cells with single letters and no matter what the letter is I want that cell to contain the word Member.

More Info: I get spreadsheets that contain, up to 40,000 rows. Column B will have names in the cells. Every once in a while a column will just have an initial instead of a full name. I'm looking for a way to change every single cell containing only one single character to the word "Member." The cells that need to change could be any letter but no matter what that letter is, if it's just a single letter in a cell, it needs to change to the word "Member."

share|improve this question

4 Answers 4

Your question is hard to understand.  I’m guessing that you mean that you have a collection of cells (say, column A) that contain text values like “” (blank), A, AA, AAA, etc., and you want to display “” (blank), Member, AA, AAA, etc.  Try setting B1 to

=IF(LEN(A1)=1, "Member", A1)

and then fill the appropriate part of column B with that formula (e.g., by dragging).

share|improve this answer
    
Not a bad way to do it... I do agree the original question was very poorly written. –  Richie086 Nov 28 '12 at 21:53
    
I tried to explain the issue while keeping my post as short as possible. Anyway, I get spreadsheets that contain, up to 40,000 rows. Column B will have names in the cells. Every once in a while a column will just have an initial instead of a full name. I'm looking for a way to change every single cell containing only one single character to the word "Member." The cells that need to change could be any letter but no matter what that letter is, if it's just a single letter in a cell, it needs to change to the word "Member." Does that clear it up? –  Allan Nov 29 '12 at 13:30
    
@Allan: Yes and no. Your explanation makes sense, but I’m puzzled because you said “that’s pretty much what I meant” for @Richie086’s answer, but no such comment for me, even though my answer seems to be exactly what you want. … Except we still don’t know what you want displayed for cells that don’t contain one letter. –  Scott Nov 29 '12 at 22:21
    
@Allan: I address another ambiguity in your question in my new answer. –  Scott Nov 29 '12 at 22:27
    
@Allan: Oh, BTW, you seem to use the words “character” and “letter” interchangeably. They do not mean the same thing. All the answers here look for cells with exactly one character, even if it is a digit (e.g., ‘4’ or ‘2’) or a “special character” (e.g., ‘@’ or ‘#’). –  Scott Nov 29 '12 at 22:33

Not sure if you are saying you want the cell that has a single letter ('a' for example) to change to the word Member or not, but here is what I think you are trying to do

  A B
1 a =IF(A1="a","Member","")
2 a =IF(A2="a","Member","")
3 b ..
4 c ..

the resulting excel spreadsheet would look like this for each cell going down vertically that contains the lowercase letter 'a'

  A B
1 a Member
2 a Member
3 b 
4 c 
share|improve this answer
    
Richie086, that's pretty much what I meant but let me explain further. I get spreadsheets with say, 10,000 to 40,000 rows. Column B will have names in the cells. Every once in a while a column will just have an initial instead of a full name. I don't want to look at each row and change them manually to "Member" because it takes forever. So I'm looking for a way to change every single cell containing only one character to "Member." Your solution works if there's just "A" in the cells with one character but the cells in question could contain any letter. –  Allan Nov 29 '12 at 13:23

You say that you want to use a formula, but also that you want to “change … cells …”.  This is a combination that generally doesn’t work.  Here are three things that are feasible:

  1. A formula to display a value in an additional column in which single-character values are replaced by “Member”.
  2. A procedure to actually change the values in the cells.
  3. A VBA subroutine to actually change the values in the cells.

The other answers that have been posted so far are variations of option 1.

If you want to do this just a few times, option 2 is probably less work than option 3.  If you want to do it many times, option 3 is probably less work than option 2.

Here’s an option 2 solution for you: Select the data that you want to change (in your specific case, this would be Column B).  Bring up the “Find and Replace” dialog box (e.g., by typing Ctrl+H).  For the “Find what” field, enter ?.  For the “Replace with” field, enter Member.  Check the “Match entire cell contents” checkbox.  Click on “Replace All”.

share|improve this answer

You may ave solved this one already, but I just stumbled across it looking for something else.

Have you tried Ctrl+H in Options? It lets you search entire cells for a single letter, though you would have to repeat this for each letter of the alphabet.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.