How to search excel document to find string and return number of row?

So I am trying to access an excel file, find a certain string determined by the user input, and give me a variable with the value of its row. I'm not sure if this actually possible but I feel like it should. Say this is my excel document:

1. Number Name Type 1 Type 2 Weakness 1
2. 1 bulbasaur grass poison fire

...etc for the first gen pokemon. I'm looking to have the user input the name of the pokemon, then have the code search for the string, and tell me which row it is in. My goal is to have this so I don't have to hard code every single pokemon in the list. I've used [num,txt] = xlsread('pokemon'); to get the data, then validate string to set a variable equal to the string. But then I'm looking to get a number or text value for the row. So say I'm looking for bulbasaur, it should return a 2, and any other pokemon down the list will give the row number. Any help? I know I don't have much actual code here, because I'm on a different computer. But I don't think I've found the right command to do this anyway. Thanks!!

Answer 1

[Abhiram Bhanuprakash]

Hi Peter,

For your requirement, I think you can use the raw data given as output by xlsread.

In your example, if you execute the following code:

in = input('Enter pokemon name as string\n');
[num,txt,raw] = xlsread('Book1.xlsx');
p = strcmpi(in,raw(:,2));% Compare user input string with entries in the Excel sheet
rowNum = find(p==1)%Get Row number

You will see that it gives the row number 2 if you give 'bulbasaur' as input. I have attached the Excel sheet as well for your reference.

Doc links for the functions used are below:

- input: http://www.mathworks.com/help/matlab/ref/input.html

- xlsread: http://www.mathworks.com/help/matlab/ref/xlsread.html

- strcmpi: http://www.mathworks.com/help/matlab/ref/strcmpi.html

- find: http://www.mathworks.com/help/matlab/ref/find.html

Hope this helps,

Cheers!

Abhiram

[Sachin Nath P M]

what if we dont know the total number of rows??

[Precious Chukwuezi]

Thank you so much. I found this really helpful

Answer 2

[Guillaume]

Another option to Abhiram's answer is to load the data into a table with readtable. It has the advantage of keeping the headers of your excel file and may make the indexing of column easier:

searchname = 'Bulbasaur';
pokemons = readtable('Book1.xlsx', 'ReadVariableNames', true);
selectedrow = strcmp(pokemons.Name, searchname)