[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『変換〜変換』（初心者です）
A B C 1 A01-01 A001-001 A1-1 2 B100-01 B100-001 B100-1 3 ZZ1-01 ZZ001-001 ZZ1-1
Ａ列に上記のようなアルファベットと数字の混じったデータがあります。（数千あります）
Ｂ列に数字のみを３桁に・Ｃ列はゼロを抜いたものを出したいのですが？
Ａ列に入力した時にＢとＣを一度に出せるマクロ式はあるのでしょうか？それとも通常の式でも？？
編集＞検索＞置き換え でできると思います。
少しなが〜〜〜くなりますが、、、
B1=SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("-",A1)-1),LOOKUP(10^5,--RIGHT(LEFT(A1,FIND("-",A1)-1),ROW($A$1:$A$4))),""),0,"")&TEXT(LOOKUP(10^5,--RIGHT(LEFT(A1,FIND("-",A1)-1),ROW($A$1:$A$4))),"000")&TEXT(--SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)-1),""),"000")
C1=SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("-",A1)-1),LOOKUP(10^5,--RIGHT(LEFT(A1,FIND("-",A1)-1),ROW($A$1:$A$4))),""),0,"")&LOOKUP(10^5,--RIGHT(B1,ROW($A$1:$A$4)))&--SUBSTITUTE(A1,LEFT(A1,FIND("-",A1)-1),"")
（川野鮎太郎）
ユーザー定義関数。
標準モジュールに登録後、セルへ
=ST(セルアドレス,分割文字列,指定桁数)
のように通常の関数のように指定。
=st(A1,"-",3) = A001-001
=st(A1,"-",1) = A1-1
のような結果になります。
Function ST(ByVal Target As Range, ByVal Splitter As String, ByVal Digit As Long) As Variant
Dim VarStr As Variant, i As Long, j As Long
Dim StrChk() As String, strTmp1 As String, strTmp2 As String
Application.Volatile
If Target.Count = 1 And Digit >= 1 And Len(Splitter) > 0 Then
VarStr = Split(Target, Splitter)
ReDim StrChk(UBound(VarStr))
For i = 0 To UBound(VarStr)
For j = 1 To Len(VarStr(i))
StrChk(i) = Right(VarStr(i), j)
If Not IsNumeric(StrChk(i)) Then Exit For
strTmp1 = StrChk(i)
strTmp2 = Replace(VarStr(i), strTmp1, "")
Next j
ST = ST & strTmp2 & Format(Val(strTmp1), WorksheetFunction.Rept("0", Digit)) & Splitter
Next i
ST = Left(ST, Len(ST) - 1)
Else
ST = CVErr(xlErrValue)
End If
End Function
習作なので、改善できる点があればご指摘下さい。
(みやほりん)(-_∂)b
なんとなく、 Function ST(略) As String ↓ Function ST(略) As variant
ST = "#VALUE!" ↓ ST = CVErr(xlErrValue) BJ
やはり、エラー値を返す関数がありましたか。BJさんありがとう。 ユーザー定義関数でmsgbox使うと再計算のたびにメッセージが出てうるさいですね。 上記コード、修正しました。 (みやほりん)(-_∂)b
ご希望とは、異なるもので、どうしようかと迷いましたが、書いてみます。 このように長くなっています。 (6UP)
B1に
=IF(SUM(COUNTIF(A1,"*-*"),COUNT(RIGHT(A1)),COUNT(LEFT(A1))=0)<3,"",
LEFT(A1,LEN(A1)-COUNT(-RIGHT(SUBSTITUTE(A1,"-","."),{1,2,3,4,5,6,7})))
&TEXT(LOOKUP(9^9,INT(RIGHT(SUBSTITUTE(A1,"-","."),{1,2,3,4,5,6,7}))),"000-")
&RIGHT(SUBSTITUTE(A1,"-","000"),3))
C1に
=IF(SUM(COUNTIF(A1,"*-*"),COUNT(RIGHT(A1)),COUNT(LEFT(A1))=0)<3,"",
LEFT(A1,LEN(A1)-COUNT(-RIGHT(SUBSTITUTE(A1,"-","."),{1,2,3,4,5,6,7})))
&LOOKUP(9^9,INT(RIGHT(SUBSTITUTE(A1,"-","."),{1,2,3,4,5,6,7})))
&-RIGHT(SUBSTITUTE(A1,"-","000"),3))
みやほりんさん、 Volatile は必要ないのでは？ (seiya)
数式でもう一案。
と言っても、数字部分はお二方から出てるので割愛して、頭のアルファベット部分だけですけど。
2文字以内ならOK。それ以上なら ,4,5,6 と続けてくださいまし。
=LEFT($A1,MIN(IF(ISNUMBER(--MID($A1,{1,2,3},1)),{1,2,3}-1,"")))
ただ、アルファベットが何文字続くか分らなければ、使えない...<<大いなる弱点>>
(sin)
シートモジュールで考えてみました。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyVal As Variant, x(1 To 2) As Variant
With Target
If .Count > 1 Then Exit Sub
If Intersect(.Cells, Range("A:A")) Is Nothing Then Exit Sub
End With
With CreateObject("VBScript.RegExp")
.Pattern = "^([A-Za-z]+)(\d+)(-|−)(\d+)$"
If .Test(Target) Then
x(1) = .Replace(Target, "$1") & Format(.Replace(Target, "$2"), "000") & .Replace(Target, "$3") & Format(.Replace(Target, "$4"), "000")
x(2) = .Replace(Target, "$1") & Format(.Replace(Target, "$2"), "0") & .Replace(Target, "$3") & Format(.Replace(Target, "$4"), "0")
End If
End With
Application.EnableEvents = False
Target.Offset(0, 1).Resize(1, 2) = x()
Application.EnableEvents = True
End Sub
（キリキ）（〃⌒o⌒)b
こんにちは〜♪
私も作ってみました。
B1セル
=IF(A1="","",LEFT(A1,MATCH(1,INDEX(ISNUMBER(MID(A1,ROW($1:$20),1)*1)*1,),0)-1)
&TEXT(RIGHT(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND("-",A1)+1),""),FIND("-",A1)-
MATCH(1,INDEX(ISNUMBER(MID(A1,ROW($1:$20),1)*1)*1,),0)),"000")&TEXT(RIGHT
(A1,LEN(A1)-FIND("-",A1)+1),"000"))
C1セル
=IF(A1="","",LEFT(A1,MATCH(1,INDEX(ISNUMBER(MID(A1,ROW($1:$20),1)*1)*1,),0)-
1)&RIGHT(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND("-",A1)+1),""),FIND("-",A1)-MATCH
(1,INDEX(ISNUMBER(MID(A1,ROW($1:$20),1)*1)*1,),0))*1&RIGHT(A1,LEN(A1)-FIND("-
",A1)+1)*1)
。。。。Ms.Rinでした〜♪♪
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.