列:定额编号 nchar(10) not null
该列有的字符串含有有:
10001
10303a
20020
90034
100042
水利40101-1
水利50006
省水9-4
安C8-357我想实现的是读取10001、10303a、20020、90034、100042、水利40101-1、水利50006
这些字符串中间的数字,比如:10001读取为10001、10303a读取为10303、水利40101-1读取为40101
然后对用读出的数字除以10000,根据商的整数部分归类,怎么实现下面Excel中公式的功能?
其他的字段(如:省水9-4、安C8-357)判定为“其他”类别Excel中的公式是:(B376为单元格)
=IF(INT(B376/10000)=1,"土",IF(INT(B376/10000)=2,"石",IF(INT(B376/10000)=3,"砌",IF(INT(B376/10000)=4,"砼",IF(INT(B376/10000)=6,"井",IF(OR(INT(B376/10000)=5,INT(B376/10000)=7),"安",IF(INT(B376/10000)>7,"其","砼")))))))分就这么多了...全用了
该列有的字符串含有有:
10001
10303a
20020
90034
100042
水利40101-1
水利50006
省水9-4
安C8-357我想实现的是读取10001、10303a、20020、90034、100042、水利40101-1、水利50006
这些字符串中间的数字,比如:10001读取为10001、10303a读取为10303、水利40101-1读取为40101
然后对用读出的数字除以10000,根据商的整数部分归类,怎么实现下面Excel中公式的功能?
其他的字段(如:省水9-4、安C8-357)判定为“其他”类别Excel中的公式是:(B376为单元格)
=IF(INT(B376/10000)=1,"土",IF(INT(B376/10000)=2,"石",IF(INT(B376/10000)=3,"砌",IF(INT(B376/10000)=4,"砼",IF(INT(B376/10000)=6,"井",IF(OR(INT(B376/10000)=5,INT(B376/10000)=7),"安",IF(INT(B376/10000)>7,"其","砼")))))))分就这么多了...全用了
设你的数据在A列,替换后的数据写在B列:
按Alt+F11打开VBA,插入一个模块,在模块中输入以下代码:
Sub getnum()
Dim reg As Object '定义对象
Dim arr '定义数组
Dim i As Long, j As Long
i = Range("A65536").End(xlUp).Row '取A列最后非空行数
Columns("B").ClearContents '清空B列
arr = Range("A1.A" & i) 'A列数据赋给数组
Set reg = CreateObject("VBscript.RegExp") '调用正则
With reg '定义正则
.Global = True
.ignorecase = True
.Pattern = "[^\d]+" '正则表达式,非数字
End With
For j = 1 To i
Range("B" & j) = reg.Replace(arr(j, 1), "") '根据模拟替换字符为空串
Next
End Sub
执行该模块,即实现替换
单就对10001a截取字符10001怎么做?
Sub getnum()
Dim reg As Object '定义对象
Dim arr '定义数组
Dim i As Long, j As Long
i = Range("A65536").End(xlUp).Row '取A列最后非空行数
Columns("B").ClearContents '清空B列
arr = Range("A1.A" & i) 'A列数据赋给数组
Set reg = CreateObject("VBscript.RegExp") '调用正则
With reg '定义正则
.Global = True
.ignorecase = True
.Pattern = "-[\d]+|[^\d]+" '正则表达式,非数字,或短模及后面的数字
End With
For j = 1 To i
Range("B" & j) = reg.Replace(arr(j, 1), "") '根据模拟替换字符为空串
Next
End Sub
10001 10001
10303a 10303
20020 20020
90034 90034
100042 100042
水利40101-1 40101
水利50006 50006
省水9-4 9
安C8-357 8
用SQL做:
create table tb(id nvarchar(10))
insert into tb select '10001'
insert into tb select '10303a'
insert into tb select '20020'
insert into tb select '90034'
insert into tb select '100042'
insert into tb select '水利40101-1'
insert into tb select '水利50006'
insert into tb select '省水9-4'
insert into tb select '安C8-357'
go
select substring(id,n,c-n) from(
select *,(select min(number) from master..spt_values where type='p' and number between b.n and 100 and substring(b.id,number,1) not between '0' and '9')c
from(
select id,
(select min(number) from master..spt_values where type='p' and number between 1 and 100 and substring(a.id,number,1) between '0' and '9')n
from tb a
)b)t
/*----------
10001
10303
20020
90034
100042
40101
50006
9
8(9 行受影响)*/
go
drop table tb