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
Does your workbook have a header?
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.
