Excel 2007 - 生成基于文本的唯一ID? [英] Excel 2007 - Generate unique ID based on text?
问题描述
最好不要真的使用VBA。但是,如果我必须这样做。
没有VBA的解决方案
基于前8个字符的逻辑+单元格中的字符数。
= CODE(cell)
它返回第一个字母的代码号
= CODE(MID(cell,2,1))
返回代码第二个字母的数字
= IFERROR(CODE(MID(cell,9,1))
如果第9个字符不存在,然后返回0
= LEN(cell)
单元格中的字符数
连接第8个代码+最后添加字符长度
如果8个字符不够,则为下一个字符复制附加代码在一个字符串中。
最终功能:
= CODE )及IFERROR(CODE(MID(B2,2,1)),0)及IFERROR(CODE(MID(B2,3,1)),0)及IFERROR(CODE(MID(B2,4,1 )),0)及IFERROR(CODE(MID(B2,5,1)),0)及IFERROR(CODE(MID(B2,6,1)),0)及IFERROR(CODE(MID (B2,7,1)),0)和IFERROR(CODE(MID(B2,8,1)),0)& LEN(B2)
I have a sheet with a list of names in Column B and an ID column in A. I was wondering if there is some kind of formula that can take the value in column B of that row and generate some kind of ID based on the text? Each name is also unique and is never repeated in any way.
It would be best if I didn't have to use VBA really. But if I have to, so be it.
Solution Without VBA.
Logic based on First 8 characters + number of character in a cell.
= CODE(cell)
which returns Code number for first letter
= CODE(MID(cell,2,1))
returns Code number for second letter
= IFERROR(CODE(MID(cell,9,1))
If 9th character does not exist then return 0
= LEN(cell)
number of character in a cell
Concatenating firs 8 codes + adding length of character on the end
If 8 character is not enough, then replicate additional codes for next characters in a string.
Final function:
=CODE(B2)&IFERROR(CODE(MID(B2,2,1)),0)&IFERROR(CODE(MID(B2,3,1)),0)&IFERROR(CODE(MID(B2,4,1)),0)&IFERROR(CODE(MID(B2,5,1)),0)&IFERROR(CODE(MID(B2,6,1)),0)&IFERROR(CODE(MID(B2,7,1)),0)&IFERROR(CODE(MID(B2,8,1)),0)&LEN(B2)
这篇关于Excel 2007 - 生成基于文本的唯一ID?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!