如標題,假設A1=a0001,怎樣才可以分開成 B1=a, C1=0001
為什麽以下公式不能分開它們? 煩高手幫幫手. 謝謝謝
B1=LEFT(A1,LEN(A1)-LEN(-LOOKUP(,-RIGHT(A1,ROW($1:$15)))))
C1=-LOOKUP(,-RIGHT(A1,ROW($1:$15)))
excel 英文字母與數字分開
2013-06-24 5:54 am
回答 (3)
2013-06-27 12:31 am
✔ 最佳答案
ABC1JFQ83668366JFQ2NOJ309490 309490NOJ3QI0106401064QI4MEW055055MEW5KJ236124236124KJ6FQM965493965493FQM7QOR918424918424QORB1=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),99)C1=LEFT(A1,(LEN(A1)-LEN(B1)))
2013-06-26 12:37 am
' Add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the VBE
' Usage =GetReference("AA001", True) = "AA"
' Usage =GetReference("AA001", False) = "001"
Public Function GetReference(ByVal value As String, ByVal isText As Boolean) As String
Dim reg As New RegExp
If (isText) Then
reg.Pattern = "[0-9]+"
Else
reg.Pattern = "[^0-9]+"
End If
GetReference = reg.Replace(value, "")
End Function
2013-06-26 17:07:47 補充：
use the answer from "bb", it is a lot easy to understand and implement.
select Tools | Macro | Visual Basic Editor
select Insert | Module |
select Tools | References | Microsoft VBScript Regular Expressions 5.5
copy and paste the preceding function
reg.Pattern = "[0-9]+" delete all numeric
' Usage =GetReference("AA001", True) = "AA"
' Usage =GetReference("AA001", False) = "001"
Public Function GetReference(ByVal value As String, ByVal isText As Boolean) As String
Dim reg As New RegExp
If (isText) Then
reg.Pattern = "[0-9]+"
Else
reg.Pattern = "[^0-9]+"
End If
GetReference = reg.Replace(value, "")
End Function
2013-06-26 17:07:47 補充：
use the answer from "bb", it is a lot easy to understand and implement.
select Tools | Macro | Visual Basic Editor
select Insert | Module |
select Tools | References | Microsoft VBScript Regular Expressions 5.5
copy and paste the preceding function
reg.Pattern = "[0-9]+" delete all numeric
2013-06-24 6:26 am
為什麼那麼複雜?
b1 = LEFT(A1,1) 就可 以了
C1 = RIGHT(A1,LEN(A1)-1) 就可 以了
b1 = LEFT(A1,1) 就可 以了
C1 = RIGHT(A1,LEN(A1)-1) 就可 以了
收錄日期: 2021-04-16 15:55:03
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20130623000051KK00293