1

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."

Improve this question

4 Answers 4

Reset to default
2

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).

Improve this answer
5
  • Not a bad way to do it... I do agree the original question was very poorly written.
    – Richie086
    Commented Nov 28, 2012 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
    Commented Nov 29, 2012 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 - Слава Україні
    Commented Nov 29, 2012 at 22:21
  • @Allan: I address another ambiguity in your question in my new answer.
    – Scott - Слава Україні
    Commented Nov 29, 2012 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 - Слава Україні
    Commented Nov 29, 2012 at 22:33
1

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
Improve this answer
1
  • 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
    Commented Nov 29, 2012 at 13:23
1

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”.

Improve this answer
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .