How to keep certain number but remove the remaining text in excel?-CodePudding

I need to keep numbers in the red box and split or delete what's behind. And I have thousands of data like that with different lengths. Can someone show me how to split that in excel?

Here are some of the data.

004193025.jpg
004193037臭鼬奇蹟一發-動物造型屁.jpg
004193042動物的吶喊.jpg
004193052   新銅管樂器小物包.jpg
004203015 日本神像大全.jpg
004203016誰得俺得系列-人形_拖車.jpg
004204001誰得俺得系列-骷顱機體P2.jpg
004204002香菇夾.jpg
004212001日本風格和食器P2.jpg
004212018寵物們的窩.jpg
004212021富士冰.jpg
004212023貓與櫃子P2.jpg
004212024趣味小雞賽跑競賽.jpg
004212030桌上迷你遊戲盤-3D AceP2.jpg
004212034 可動式眼鏡-ALL STYLE篇.jpg
004212036新銅管樂器吊飾P15.jpg
004213007可動生物系列-動感蠍子P2.jpg
004213008 藥袋造型口罩套.jpg
004213009收音機造型提袋_錄音帶小物包.jpg
004213011日本塑膠袋造型環保袋.jpg

CodePudding user response：

I figured out the easiest way without a formula.

copy the first section of the number to B1
Select B2, then press Ctrl E

CodePudding user response：

Something like this should work:

=SUBSTITUTE(TEXTJOIN("p",,IF(ISNUMBER(MID(L4,SEQUENCE(len(A2)),1) 0),MID(L4,SEQUENCE(len(a2)),1),"p")),"p","")