Assign a number to a letter in excel

2 views (last 30 days)
Starg0rl
Starg0rl on 29 Jan 2020
Edited: Matt Tearle on 30 Jan 2020
I have a column on my excel spreadsheet (which i have loaded into matlab) where each cell contains either A B or C.
For example: X1 = [ A B A A A A A B C A B A A A B C A B A A A A A A B C ]'
I would like to extract from this column [A B] or [A B C]
I thought about assigning 1 to A, 2 to B, 3 to C so that the following column will then be as follows:
X2 = [1 2 1 1 1 1 1 2 3 1 2 1 1 1 2 3 1 2 1 1 1 1 1 1 2 3]'
from there, I would then use diff(X2) to get the consecutive values.
I am having trouble assigning the numbers to the letters. Is there a way of doing this? or is there a better way of going about this?

Answers (2)

Matt Tearle
Matt Tearle on 29 Jan 2020
I don't understand what you're trying to get out of the original data. If you do diff(X2) you'll get
[1 -1 0 0 0 0 1 1 -2 1 -1 0 ...]
What would that mean (in terms of letters A, B, C)?
In the example you give (X1), what would the expected output be, and why?
Are you trying to remove repeats? That is, the result for X1 would be
[A B A B C A B A B C A B A B C]
If so, I'd suggest making sure X1 is string or categorical, then doing this:
Y = X1; % make copy (so X1 isn't altered)
Y(X1(1:end-1) == X1(2:end)) = [] % remove repeats (value is same as previous value)
  2 Comments
Starg0rl
Starg0rl on 30 Jan 2020
X2 would be the expected output of X1 (after letters have been turned to numbers)
Sorry, i meant to say i would then use find(diff(x2==1)) to find where the values are consecutive.
"A B and C" represent groups of data on excel, so i need to find their indices so that i can find the data that belongs to them on another column in excel.
Matt Tearle
Matt Tearle on 30 Jan 2020
Edited: Matt Tearle on 30 Jan 2020
"i need to find their indices so that i can find the data that belongs to them on another column in excel"
OK, so what's the final goal? Because what you're describing there sounds like logical indexing:
X1 = [A A B A C A B B A C] % the groups
Y = [4 6 2 7 4 4 6 2 1 7] % the other data
>> Y(X1 == "A")
ans =
4 6 7 4 1
>> Y(X1 == "B")
ans =
2 6 2
>> Y(X1 == "C")
ans =
4 7
Can you give a simple example of what you're trying to get at the end?
If you really just want to turn letters into numeric values, you can use something like what Fangjun Jiang suggested (subtract 64 from the ASCII char value) or if you make X1 categorical, you can just do double(X1). But I don't think that's your ultimate goal, so there may be a better approach.

Sign in to comment.


Fangjun Jiang
Fangjun Jiang on 29 Jan 2020
X1={'A','B','C'}';
y=cell2mat(X1)-64

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!