Hi, all,
I have a working formula which strips "." or "-" or "/" or returns the original text if none of these are found.
I need to turn this into a custom function, and can't figure out where I'm stuffing up...
The (really ugly, but working!) formula is:
=IF(ISERROR(RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))))),A1,LEFT(A1,IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))-1)&RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))))
and my (best?)attempt at converting it is :
Public Function thStrip(InVal As String) As String
Dim strOutVal As String
Dim thFind As Excel.WorksheetFunction
Set thFind = Excel.Application.WorksheetFunction
With thFind
strOutVal = IIf(IsError(Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), _
.Find("-", InVal), IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))))), InVal, _
Left(InVal, IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), _
.Find(".", InVal))) - 1) & "" & Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), .Find(".", InVal)))))
End With
thStrip = strOutVal
End Function
I suspect the problem may be in the concatenation, the use of IIf, IsError, or the way I have used Find...but I don't really
know !
I am trying to break it down and try and isolate the problem, but I haven't had any luck yet !
You may wonder why bother if the formula works, but I want to use the function in the design grid of a query, as in
thStrip(SuchandSuchField). I could bring back the data and then use the formula, but I need it for 24 columns with 1000+ rows, and this would be a more satisfying approach !
In any field there could be:
B9567.09 need:B956709
M20/4 need:M204
Z74-8 need:Z748
81.400 need:81400
I10 need:I10
gh/lo917 need:ghlo917
kl/7259 need:kl7259
100878-009 need:100878009
Any advice at all would be greatly appreciated !
t
I have a working formula which strips "." or "-" or "/" or returns the original text if none of these are found.
I need to turn this into a custom function, and can't figure out where I'm stuffing up...
The (really ugly, but working!) formula is:
=IF(ISERROR(RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))))),A1,LEFT(A1,IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))-1)&RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))))
and my (best?)attempt at converting it is :
Public Function thStrip(InVal As String) As String
Dim strOutVal As String
Dim thFind As Excel.WorksheetFunction
Set thFind = Excel.Application.WorksheetFunction
With thFind
strOutVal = IIf(IsError(Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), _
.Find("-", InVal), IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))))), InVal, _
Left(InVal, IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), _
.Find(".", InVal))) - 1) & "" & Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), .Find(".", InVal)))))
End With
thStrip = strOutVal
End Function
I suspect the problem may be in the concatenation, the use of IIf, IsError, or the way I have used Find...but I don't really
know !
I am trying to break it down and try and isolate the problem, but I haven't had any luck yet !
You may wonder why bother if the formula works, but I want to use the function in the design grid of a query, as in
thStrip(SuchandSuchField). I could bring back the data and then use the formula, but I need it for 24 columns with 1000+ rows, and this would be a more satisfying approach !
In any field there could be:
B9567.09 need:B956709
M20/4 need:M204
Z74-8 need:Z748
81.400 need:81400
I10 need:I10
gh/lo917 need:ghlo917
kl/7259 need:kl7259
100878-009 need:100878009
Any advice at all would be greatly appreciated !
t