I have an Excel sheet with column H containing IDs. Unfortunately some people put their IDs to be two letters then 5 numbers (AB12345) and some just put the 5 numbers (12345). I need to go through the whole column and split any entry with letters, so I eventually have a column with all of them being just the 5 numbers.
我有一张Excel表格,其中H列包含ID。不幸的是,有些人将他们的ID分为两个字母,然后是5个数字(AB12345),有些人只是将5个数字(12345)。我需要浏览整个列并用字母拆分任何条目,所以我最终有一个列,其中所有列都只是5个数字。
2 个解决方案
#1
3
With data in column A, in B1 enter:
对于A列中的数据,在B1中输入:
=IF(LEN(A1)=7,RIGHT(A1,5),A1)
and copy down
并复制下来
#2
0
If you do not know where the alphanumeric characters are in the string, you could use this array formula to get just the numeric portion.
如果您不知道字符串中的字母数字字符在哪里,则可以使用此数组公式来获取数字部分。
=TEXTJOIN("", TRUE, IFERROR(MID(A1, ROW(INDIRECT("1:100")), 1) + 0, ""))
You will have to Ctrl + Shift + Enter this formula to get it to be an array formula. That will add curly brackets. Textjoin is only available in Excel 2016 or Office 365.
您必须按Ctrl + Shift +输入此公式才能使其成为数组公式。这将添加大括号。 Textjoin仅适用于Excel 2016或Office 365。
#1
3
With data in column A, in B1 enter:
对于A列中的数据,在B1中输入:
=IF(LEN(A1)=7,RIGHT(A1,5),A1)
and copy down
并复制下来
#2
0
If you do not know where the alphanumeric characters are in the string, you could use this array formula to get just the numeric portion.
如果您不知道字符串中的字母数字字符在哪里,则可以使用此数组公式来获取数字部分。
=TEXTJOIN("", TRUE, IFERROR(MID(A1, ROW(INDIRECT("1:100")), 1) + 0, ""))
You will have to Ctrl + Shift + Enter this formula to get it to be an array formula. That will add curly brackets. Textjoin is only available in Excel 2016 or Office 365.
您必须按Ctrl + Shift +输入此公式才能使其成为数组公式。这将添加大括号。 Textjoin仅适用于Excel 2016或Office 365。