# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a very large excel spreadsheet consisting of copious amounts of data. To make a really long story short, I need to eliminate all single values in one particular column in my spreadsheet. By "single value" I am referring to single letters, such as "A", "B", "C" etc. These are the middle names of the spreadsheet, and I need to get rid of them for a specific column. Because there is so much data, it is unrealistic for me to do this manually. These single value letters are in the same column as regular word values with full last names, so I can't just delete the entire columns without getting rid of the regular names that I want to keep. So is there any kind of Excel function that allows me to delete the single values only? Thanks.
Solved by Z. D. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/09/2017 - 01:47
Hello there, I understand that you need to delete all single character cells, right?
User 26/09/2017 - 01:47
Ah, yes I do.
Excelchat Expert 26/09/2017 - 01:47
Can you give me a short example of your data set using the document preview to the right so I can provide you with an accurate solution?
Excelchat Expert 26/09/2017 - 01:48
Because normally, based on your description, all that we need is to create a helper column that tells us the length of the values.
User 26/09/2017 - 01:48
So im not sure if you saw what I just did, but do you see the coluims there?
Excelchat Expert 26/09/2017 - 01:49
For instance, if you could see the formula I wrote in column B.
Excelchat Expert 26/09/2017 - 01:49
It tells you exactly how many characters there are in column A.
Excelchat Expert 26/09/2017 - 01:49
So with this, we can now filter all the number 1.
User 26/09/2017 - 01:49
cool. what was that formula?
Excelchat Expert 26/09/2017 - 01:49
The formula I used is =Len()
Excelchat Expert 26/09/2017 - 01:50
You should see the actual formula in column B.
Excelchat Expert 26/09/2017 - 01:50
For instance, in Cell B2
Excelchat Expert 26/09/2017 - 01:50
The formula is =len(A2)
Excelchat Expert 26/09/2017 - 01:50
Which means, you want to count the number of characters of Cell A2.
Excelchat Expert 26/09/2017 - 01:50
In this case = 4.
Excelchat Expert 26/09/2017 - 01:51
So you can now drag the formula to the bottom to apply it automaticall per row.
User 26/09/2017 - 01:51
i see
Excelchat Expert 26/09/2017 - 01:51
Then, you can select cell B1.
Excelchat Expert 26/09/2017 - 01:51
Go to DATA then click FILTER.
Excelchat Expert 26/09/2017 - 01:51
Then make sure only 1 is selected.
Excelchat Expert 26/09/2017 - 01:52
Then you will have the list of ALL cells with only 1 character.
Excelchat Expert 26/09/2017 - 01:52
You can then select them and delete them all at the same time without having to manually go through the list
User 26/09/2017 - 01:52
ok. i think i understand. can we start over from the beginning just so that I can see the whole thing step by step?
Excelchat Expert 26/09/2017 - 01:52
Sure, but it think it would be best to work on your actual workbook instad.
Excelchat Expert 26/09/2017 - 01:53
Excelchat Expert 26/09/2017 - 01:53
Or does your data start at row 1?
User 26/09/2017 - 01:53
it has a header. the actual data starts at A2
Excelchat Expert 26/09/2017 - 01:54
That's great.
Excelchat Expert 26/09/2017 - 01:54
Go to Cell B1, then type something.
Excelchat Expert 26/09/2017 - 01:54
Anything will do as you will just be naming that column.
Excelchat Expert 26/09/2017 - 01:54
Then type this in cell B2
Excelchat Expert 26/09/2017 - 01:54
=len(A2)
User 26/09/2017 - 01:54
ok one second
Excelchat Expert 26/09/2017 - 01:55
If done correctly, you will get a number and that corresponds to the number of characters your A2 has.
User 26/09/2017 - 01:55
ok i got it
Excelchat Expert 26/09/2017 - 01:55
Next, select cell B2.
Excelchat Expert 26/09/2017 - 01:55
Then drag it downwards.
Excelchat Expert 26/09/2017 - 01:55
Do you know how to flash fill?
User 26/09/2017 - 01:56
yeah i just double clicked the small square and it populates the rest right?
Excelchat Expert 26/09/2017 - 01:56
That's right!
Excelchat Expert 26/09/2017 - 01:56
Now, do you have column B all filled up?
User 26/09/2017 - 01:56
ok im followign you so far
Excelchat Expert 26/09/2017 - 01:56
Next, select cell B1.
User 26/09/2017 - 01:56
yes. the column now has numbers
Excelchat Expert 26/09/2017 - 01:56
Go to DATA.
Excelchat Expert 26/09/2017 - 01:56
Click FILTER.
User 26/09/2017 - 01:56
ok
Excelchat Expert 26/09/2017 - 01:57
Your cell B1 will have an arrow next to it.
Excelchat Expert 26/09/2017 - 01:57
Click on it then UNCHECK select all.
User 26/09/2017 - 01:57
ok
Excelchat Expert 26/09/2017 - 01:57
Then put a check in 1
Excelchat Expert 26/09/2017 - 01:57
This will have everything UNCHECKED except for 1.
Excelchat Expert 26/09/2017 - 01:57
Click OK.
Excelchat Expert 26/09/2017 - 01:57
You will now have all the rows with only 1 character.
User 26/09/2017 - 01:58
ok so it partially worked. there are still some names with more than one character
Excelchat Expert 26/09/2017 - 01:58
I'm not sure I understand.
Excelchat Expert 26/09/2017 - 01:59
Can you just send your file over so I can take a look at it?
Excelchat Expert 26/09/2017 - 01:59
You can click the paper clip icon next to this chat and select your file.
User 26/09/2017 - 01:59
This file contains PHI I cannot send it because of HIPAA regulations
User 26/09/2017 - 01:59
however, I can copy over specific names
Excelchat Expert 26/09/2017 - 01:59
I see.
User 26/09/2017 - 01:59
give me one second ill show you
Excelchat Expert 26/09/2017 - 01:59
Okay, please paste over the problem data you have.
User 26/09/2017 - 02:00
So the single characters should be deleted. thats what i want
User 26/09/2017 - 02:01
let me try what you said
Excelchat Expert 26/09/2017 - 02:01
Single characters of which column?
User 26/09/2017 - 02:01
B2
Excelchat Expert 26/09/2017 - 02:01
Let me do it for now as we are running out of time.
User 26/09/2017 - 02:01
ok
Excelchat Expert 26/09/2017 - 02:02
So I've deleted them.
Excelchat Expert 26/09/2017 - 02:02
May I know where the problem is after deleting the 1s?
User 26/09/2017 - 02:03
yeah it seems like I made a mistake with the formula earlier, but now i see this is what i need
Excelchat Expert 26/09/2017 - 02:03
That's great!
Excelchat Expert 26/09/2017 - 02:03
Once you've cleared the ones you don't need, you can delete column C.
User 26/09/2017 - 02:04
wonderful. i think we are done here. thanks for the help
Excelchat Expert 26/09/2017 - 02:04
I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert 26/09/2017 - 02:04
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User 26/09/2017 - 02:04
sure. this is my first time using this website so im still learning hwo to navigate

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.