google sheets convert text to number This is a topic that many people are looking for. star-trek-voyager.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, star-trek-voyager.net would like to introduce to you Google Forms: Bonus – Converting Text Entry to Numbers for Analysis. Following along are instructions in the video below:
“Everybody here s a little bonus video for you you may have noticed that google google forms saves. The responses in a text format such as not at all and little when you re doing statistical analysis on survey. Results or entered data. You re probably going to want that to be a number so for instance.
I love it might be a 5 not at all might be a 1 so i m gonna show you today two different ways that you can convert the data in this spreadsheet from text to numbers. The first is the simplest way and that s just to use find and replace so in excel or you can do this in google sheets as well under the edit menu choose find and then select replace all right so i m gonna look at column c. Here and i m gonna replace the word female with the number 1 so if i say replace all what it will do is it will convert the two words female into one now. I m gonna come here.
And do male and convert male to 2 and again replace all now be careful when you re doing replace all for instance. I have female and male if i had converted male first let me just show you how that works let s say. I m doing the same find and replace and i decided to do male first and replace it with 2 and i choose replace all you ll see that what it did was it converted the word male to a 2. But female also contains the word male and it converted that to a 2 as well so be careful about the texts that you re using because they may not always do what you want them to do and that s why find and replace works well for some things.
But not for everything right because of this i want to show you an alternate method. And that s using the vertical lookup tool. And that s done using a separate spreadsheet tab so down at the bottom. I m just choose insert sheet and here.
I m going to put all of my possibilities for my second question you might remember that in my original form these were my choices not at all a little a lot and i love it so i need to come up with numerical equivalence for those so i m going to type in not at all all right so i have all of those written down and not at all will be one a little will be two a lot will be three and i love it will be 4 all right so this is my very first one. I can even insert a row here and give it a header and call it likert questions. So that s what i have there down here. I might make another one.
Called yes. No questions of which yes will be one no will be two and maybe. If there is such a thing will be three all right so i ve just created all of these different answer sets make these bold to make them a little easier to see all right once i ve done this the next. Important thing is that i need to select each table like this and sort the first column in alphabetical order.
I know that seems strange. But in order for this formula to work that s what you need to do so i ve just selected this little bit here and under data. I m gonna choose sort and i ll sort by like your questions and okay so now let s put them in order. I m gonna do the same thing here highlight this sort a sort by the column.
A and there we go so. Now my columns are all sorted. And i m ready to go through and do my formula now so let s drop back into our initial form. And i m just gonna create a new column here so i ll select this column.
I ll do a ctrl click and say insert and it s just gonna insert a blank column for me now. My formula is a vlookup or a vertical lookup so i m going to say. Equals vlookup. And what s the value that i m looking up well.
The value is going to be whatever answer is here where and then i ll do a comma because that s what it says to do here. Now the next thing is where am i going to look this up so i m going to come down to the bottom and click on sheet2 and i m going to select this table here notice. I ve not included the header up here for this table. Just the answers that i want and then when i hit comma again.
The next one it s going to ask me is for my column index number that s the number that i want it to deliver. Which is this column here and you would think that that s column b. But actually. It is the second column of the table array.
So the number that i m going to put is 2 all right so i have vlookup the cell that i want to look up. Which is d2. My table array which is on the second sheet and that s a through b5. And then which column do i deliver and that s the number 2 and i m just gonna hit enter and now you can see that it is delivered that number now i d like to copy this all the way down.
But you can see when i do that what it ll do is it ll change my table array and i don t want that to happen my table always needs to be a 2 through b5 for this type of question. So how can i fix that i m going to go back to the original one and all you need to do is in front of the a type a dollar sign in front of the two type of dollar sign and do the same thing for b5. And then enter. What that does is that makes it an absolute reference as opposed to a relative reference and your formulas going to work.
So now. When i copy it down. You ll see that it auto fills numbers for each of those and now. I have my number in this column.
So here s my text and here s my corresponding number alright let s do the same thing here we re gonna insert and this time. I can just copy these and paste them in so those are the two ways that you could substitute numbers for the text that you get through google. ” ..
Thank you for watching all the articles on the topic Google Forms: Bonus – Converting Text Entry to Numbers for Analysis. All shares of star-trek-voyager.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.