[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定の数字の3こ後にカンマを挿入』（杏）
セルA1に特定の数字34で始まる5桁の数字が10〜50こくらいあります。
｢＠34122＊34777○⚪34892…｣
なお、34の前には文字だったり（）だったりがあります。
これをカンマを挿入してA2に｢＠34122、＊34777、○⚪34892、…｣
もしくは数字のみA2｢34125｣ A3｢34777｣ A4｢34892｣…
A2〜に入る数式を教えていただけないでしょうか？
どちらでも助かりますし、数字のみ取り出せたら尚更助かります。
よろしくお願いします。
＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞
A列に文字列があるものとして、B1に下記の式を入れると、B1,C1,...に数値が取り出せます。
=REGEXEXTRACT(A1,"34\d{3}",1)
なお、REGEXEXTRACT関数はOffice365の全員が使えるようではないようです。 試して見て下さい。
> A2｢34125｣ A3｢34777｣ A4｢34892｣…
それなら
A2セルに
=TOCOL(REGEXEXTRACT(A1,"34\d{3}",1))
を入れると下に展開されます。
（xyz） 2025/01/25(土) 10:25:03
>34で始まる5桁の数字が10〜50こくらいあります。
REGEXEXTRACT関数が使用できる環境であれば
=REGEXEXTRACT(A1,"(?<=\D)34\d{3}",1)
＠34122＊34777○⚪34892 *2345 34122 34777 34892 (2345)は抽出せず。 （jindon） 2025/01/25(土) 10:48:11
なるほどです。 でも例は ＠34122＊34777○⚪34892 *234567 のほうが適切かもしれませんね。(もともと数字が5文字無いと排除されるので）
=TOCOL(REGEXEXTRACT(A1,"(?<=\b)34\d{3}",1))
と単語境界を使ってもよいかもしれません。
冒頭に直接5文字数字があっても排除されます。
（単語境界はふるまいが不安定なので懸念はありますが）
（xyz） 2025/01/25(土) 11:18:27
34から始まる数字6文字もあって、それを排除するなら
=TOCOL(REGEXEXTRACT(A1,"(?<=\b)34\d{3}(?=\b)",1))
などということになりそうです。
# 質問者さんそっちのけで如何なものかとは思っております。
（xyz） 2025/01/25(土) 11:27:29
Option Explicit
Sub カンマで区切る()
Dim inputStr As String
Dim outputStr As String
Dim pattern As String
Dim matches As Object
Dim match As Object
inputStr = Range("A1").Value
pattern = "(\D*34\d{3})"
Set matches = CreateObject("VBScript.RegExp")
With matches
.Global = True
.pattern = pattern
Set matches = .Execute(inputStr)
End With
For Each match In matches
outputStr = outputStr & match.Value & "、"
Next match
If Len(outputStr) > 0 Then
outputStr = Left(outputStr, Len(outputStr) - 1)
End If
Range("A2").Value = outputStr
End Sub
（暇な人） 2025/01/25(土) 11:42:13
将来使用不可になるであろうvbaでもよければ
Sub test()
Dim s, i&, m As Object
s = [a1]
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D(34\d{3})"
Set m = .Execute(s)
If m.Count Then
For i = 0 To m.Count - 1
[a2].Cells(1, i + 1) = m(i).submatches(0)
Next
End If
End With
End Sub
（jindon） 2025/01/25(土) 12:51:35
数字は必ず5桁です。
冒頭に直接5文字数字も無いです。
REGEXEXTRACT関数は無いと思います。
職場のパソコンなのでハッキリとはわかりませんが、たぶん無いです。
VBAは私がわからず、また他の人に引き継ぐことも出来ないので
数式が助かります。（数式ならなんとかいけるかなと）
勝手ばっかりで申し訳ありませんが、よろしくお願いします。
（杏） 2025/01/25(土) 13:12:06
つかぬことを伺いますが、 >セルA1に特定の数字34で始まる5桁の数字が10〜50こくらいあります。 これはどのようにして作ったものですか？ 手作業なんですか、それともなんらかのシステムの作成したものなんですか？
（なんらかの解決につながる規則性のヒントになることがあればと思ってお尋ねするものです。)
（xyz） 2025/01/25(土) 13:42:41
＞職場のパソコンなのでハッキリとはわかりませんが、たぶん無いです。
と言うことは、これも不確かなのでしょうか？ ↓ Microsoft365
バージョンによって使える関数が違うので、一番古いバージョンを書いてください。
（半平太） 2025/01/25(土) 13:48:11
（杏） 2025/01/25(土) 14:02:00
回答ありがとうございました。では月曜以降に。 （xyz） 2025/01/25(土) 14:32:34
REGEXEXTRACT関数ですが、私も自宅では使えますが、会社では使えません
Microsoft 365 Apps の更新履歴 https://learn.microsoft.com/ja-jp/officeupdates/update-history-microsoft365-apps-by-date
自宅の Microsoft365 Personal は「最新チャネル」 会社の Microsoft365 は「月次エンタープライズチャネル」になっており、 チャネルによって使えるバージョンや使える関数が違うようです
例えばGROPUBY関数やPIVOTBY関数は、最新チャネルでは バージョン 2409:9月23日 で追加されましたが、 月次エンタープライズチャネルでは最新の2411でも追加されておらず、実際にも使えないです
ちなみにREGEXEXTRACT関数は、自宅のバージョン2412最新チャネルで使えてますが、 リリースノートには何の記載もなくて、いつから使えるようになったのかな？状態です
バージョンを調べる際は、更新チャネルまで考慮する必要があるようです （´・ω・`） 2025/01/25(土) 16:30:20
なるほどチャンネルにも配慮が必要だったのですね。ありがとうございます。
たしかに色々な新関数の対応時期についてはまちまちのようです。 何かクジにあたるような感じで、徐々に対応範囲を広げているようです。 私の場合（最新チャネル）、ひとつのPCではバージョン2411でREGEXEXTRACT関数が使えるようになりましたが、 もうひとつのPCでは同じバージョンでも使えませんでした。 バージョン2412に更新後も当初は両者に差がありましたが、現在は両方とも使えるようになっています。 このあたりの対応は全く公表されていないようですね。
# VBScriptの収束に関する対応でも、当初、今後の対応策代替策をできるだけ早く公表する、 # とネット上で発信しておきながら、その発言自体を既に勝手に抹消してしまっていることが # 判明しています。 # この件に関するMS社の対応には不信感しかありません。 # 寡占状態をよいことに胡坐を掻いているのではないでしょうか。 （xyz） 2025/01/25(土) 16:59:34
A2 『特定の数字の3こ後にカンマを挿入』（杏）
セルA1に特定の数字34で始まる5桁の数字が10〜50こくらいあります。
｢＠34122＊34777○⚪34892…｣
なお、34の前には文字だったり（）だったりがあります。
これをカンマを挿入してA2に｢＠34122、＊34777、○⚪34892、…｣
もしくは数字のみA2｢34125｣ A3｢34777｣ A4｢34892｣…
A2〜に入る数式を教えていただけないでしょうか？
どちらでも助かりますし、数字のみ取り出せたら尚更助かります。
よろしくお願いします。
＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞
A列に文字列があるものとして、B1に下記の式を入れると、B1,C1,...に数値が取り出せます。
=REGEXEXTRACT(A1,"34\d{3}",1)
なお、REGEXEXTRACT関数はOffice365の全員が使えるようではないようです。 試して見て下さい。
> A2｢34125｣ A3｢34777｣ A4｢34892｣…
それなら
A2セルに
=TOCOL(REGEXEXTRACT(A1,"34\d{3}",1))
を入れると下に展開されます。
（xyz） 2025/01/25(土) 10:25:03
>34で始まる5桁の数字が10〜50こくらいあります。
REGEXEXTRACT関数が使用できる環境であれば
=REGEXEXTRACT(A1,"(?<=\D)34\d{3}",1)
＠34122＊34777○⚪34892 *2345 34122 34777 34892 (2345)は抽出せず。 （jindon） 2025/01/25(土) 10:48:11
なるほどです。 でも例は ＠34122＊34777○⚪34892 *234567 のほうが適切かもしれませんね。(もともと数字が5文字無いと排除されるので）
=TOCOL(REGEXEXTRACT(A1,"(?<=\b)34\d{3}",1))
と単語境界を使ってもよいかもしれません。
冒頭に直接5文字数字があっても排除されます。
（単語境界はふるまいが不安定なので懸念はありますが）
（xyz） 2025/01/25(土) 11:18:27
34から始まる数字6文字もあって、それを排除するなら
=TOCOL(REGEXEXTRACT(A1,"(?<=\b)34\d{3}(?=\b)",1))
などということになりそうです。
# 質問者さんそっちのけで如何なものかとは思っております。
（xyz） 2025/01/25(土) 11:27:29
Option Explicit
Sub カンマで区切る()
Dim inputStr As String
Dim outputStr As String
Dim pattern As String
Dim matches As Object
Dim match As Object
inputStr = Range("A1").Value
pattern = "(\D*34\d{3})"
Set matches = CreateObject("VBScript.RegExp")
With matches
.Global = True
.pattern = pattern
Set matches = .Execute(inputStr)
End With
For Each match In matches
outputStr = outputStr & match.Value & "、"
Next match
If Len(outputStr) > 0 Then
outputStr = Left(outputStr, Len(outputStr) - 1)
End If
Range("A2").Value = outputStr
End Sub
（暇な人） 2025/01/25(土) 11:42:13
将来使用不可になるであろうvbaでもよければ
Sub test()
Dim s, i&, m As Object
s = [a1]
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D(34\d{3})"
Set m = .Execute(s)
If m.Count Then
For i = 0 To m.Count - 1
[a2].Cells(1, i + 1) = m(i).submatches(0)
Next
End If
End With
End Sub
（jindon） 2025/01/25(土) 12:51:35
数字は必ず5桁です。
冒頭に直接5文字数字も無いです。
REGEXEXTRACT関数は無いと思います。
職場のパソコンなのでハッキリとはわかりませんが、たぶん無いです。
VBAは私がわからず、また他の人に引き継ぐことも出来ないので
数式が助かります。（数式ならなんとかいけるかなと）
勝手ばっかりで申し訳ありませんが、よろしくお願いします。
（杏） 2025/01/25(土) 13:12:06
つかぬことを伺いますが、 >セルA1に特定の数字34で始まる5桁の数字が10〜50こくらいあります。 これはどのようにして作ったものですか？ 手作業なんですか、それともなんらかのシステムの作成したものなんですか？
（なんらかの解決につながる規則性のヒントになることがあればと思ってお尋ねするものです。)
（xyz） 2025/01/25(土) 13:42:41
＞職場のパソコンなのでハッキリとはわかりませんが、たぶん無いです。
と言うことは、これも不確かなのでしょうか？ ↓ Microsoft365
バージョンによって使える関数が違うので、一番古いバージョンを書いてください。
（半平太） 2025/01/25(土) 13:48:11
（杏） 2025/01/25(土) 14:02:00
回答ありがとうございました。では月曜以降に。 （xyz） 2025/01/25(土) 14:32:34
REGEXEXTRACT関数ですが、私も自宅では使えますが、会社では使えません
Microsoft 365 Apps の更新履歴 https://learn.microsoft.com/ja-jp/officeupdates/update-history-microsoft365-apps-by-date
自宅の Microsoft365 Personal は「最新チャネル」 会社の Microsoft365 は「月次エンタープライズチャネル」になっており、 チャネルによって使えるバージョンや使える関数が違うようです
例えばGROPUBY関数やPIVOTBY関数は、最新チャネルでは バージョン 2409:9月23日 で追加されましたが、 月次エンタープライズチャネルでは最新の2411でも追加されておらず、実際にも使えないです
ちなみにREGEXEXTRACT関数は、自宅のバージョン2412最新チャネルで使えてますが、 リリースノートには何の記載もなくて、いつから使えるようになったのかな？状態です
バージョンを調べる際は、更新チャネルまで考慮する必要があるようです （´・ω・`） 2025/01/25(土) 16:30:20
なるほどチャンネルにも配慮が必要だったのですね。ありがとうございます。
たしかに色々な新関数の対応時期についてはまちまちのようです。 何かクジにあたるような感じで、徐々に対応範囲を広げているようです。 私の場合（最新チャネル）、ひとつのPCではバージョン2411でREGEXEXTRACT関数が使えるようになりましたが、 もうひとつのPCでは同じバージョンでも使えませんでした。 バージョン2412に更新後も当初は両者に差がありましたが、現在は両方とも使えるようになっています。 このあたりの対応は全く公表されていないようですね。
# VBScriptの収束に関する対応でも、当初、今後の対応策代替策をできるだけ早く公表する、 # とネット上で発信しておきながら、その発言自体を既に勝手に抹消してしまっていることが # 判明しています。 # この件に関するMS社の対応には不信感しかありません。 # 寡占状態をよいことに胡坐を掻いているのではないでしょうか。 （xyz） 2025/01/25(土) 16:59:34
A2
=LET(a,TEXTSPLIT(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;0;|")),,"|",1),
FILTER(a,(LEFT(a,2)="34")*(LEN(a)=5)))
とか
=FILTERXML("<y><x>"&SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;; ")))," ","</x><x>")&"</x></y>","//x[starts-with(.,'34') and string-length(.)=5]")
5桁にこだわらない場合は
=LET(a,TEXTSPLIT(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;0;|")),,"|",1),
FILTER(a,LEFT(a,2)="34"))
とか
=FILTERXML("<y><x>"&SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;; ")))," ","</x><x>")&"</x></y>","//x[starts-with(.,'34')]")
（んなっと） 2025/01/25(土) 17:49:35
FILTERXMLの式2つ、間違えていました。こちらで。
=FILTERXML("<y><x>"&SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;0; ")))," ","</x><x>")&"</x></y>",
"//x[starts-with(.,'34') and string-length(.)=5]")
=FILTERXML("<y><x>"&SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1)),1),"0;;0; ")))," ","</x><x>")&"</x></y>",
"//x[starts-with(.,'34')]")
（んなっと） 2025/01/26(日) 00:07:38
【以下は、勘違いでしたので、取り下げます。スルー願います。 ご指摘ありがとうございました。】(1/27追記)
正規表現関数を使わない方法を追加で記載しておきます。(既に回答がありますが） Microsoft365であればどれでも使えるはずです。
■A2セルに下記を入れます。これでA2セル以下に結果が表示されるはずです。
=LET(ary,DROP(TEXTSPLIT(A1,"34"),,1),
str,REDUCE("",ary,LAMBDA(accum,x,IF(ISNUMBER(LEFT(x,3)*1),accum & ",34" & LEFT(x,3), accum))),
TOCOL(TEXTSPLIT(RIGHT(str,LEN(str)-1),","))
)
A1ではなく、別のセルを対象にしたいのであれば、式のなかのA1(1カ所)を変更して下さい。
# なんだか、発言記事が重複してしまってますね。 （xyz） 2025/01/26(日) 18:14:53
ちょっと気になるのですが、34の後の3桁にも34が入っているなんてことはないんですかね？
例：＠34134、＊34777、○34892
（半平太） 2025/01/26(日) 19:17:46
365ならPY関数が使えるはずなので参考まで
A2を選択して =PY と入力してTabキー または 数式タブの Pythonの挿入 をクリック
#行頭の空白は削除
import re
re.findall('\D(34\d{3})',xl("A1"))
と入力して Ctrl+Enter
A2にlist と表示されたら A2を選択して右クリック Python出→Excelの値 （´・ω・`） 2025/01/26(日) 19:20:13
なるほど。私のはダメですね。取り消し扱いにしてください。
（xyz） 2025/01/26(日) 21:32:01
末尾の5桁も含めてチェック
=TOCOL(REGEXEXTRACT(A1,"(?<=\D)34\d{3}(?=\D|$)",1))
（んなっと） 2025/01/27(月) 13:56:56
34の後の3桁にも34が入っているなんてことはないんですかね？
〉あります。34034 34340 などあります。
皆様のご提案を読み返して、いろいろやってみます。
お時間ください、よろしくお願いします。
（杏） 2025/01/27(月) 19:34:18
なので、んなっとさんの方法でいけました。
ですが、SEQUENCE関数とLET関数を勉強しなきゃです。
ありがとうごさいました。
（杏） 2025/01/29(水) 19:51:27
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.