I have a column of data with inconsistent data entry format from which
I need to
extract the significant digits after the # sign.
I cannot simply use a Right function because the data I need to
extract is embedded in a text string..
FIND the # sign is only the beginning of the solution. But how do I go
from there ?
There is always a space between the last digit and the following
description text.
CO#002 Description text... Expected extraction : 2
CO# 005 xxxxxxxx... Expected extraction : 5
CO#0007 yyyyy... ... Expected extraction : 7
CO # 010 zzz.......... Expected extraction : 10
Help welcomed.

Celeste

Celeste,

I think I'd use the split function a couple of times inside a user defined
function. Try something like:
____________________________

Function MyExtract(strIn As String) As Long
arrayA = Split(strIn, "#")
arrayB = Split(LTrim(arrayA(1)), " ")
MyExtract = CLng(arrayB(0))
End Function
_______________________________

Steve Yandl



"u473" wrote in message
ups.com...
I have a column of data with inconsistent data entry format from which
I need to
extract the significant digits after the # sign.
I cannot simply use a Right function because the data I need to
extract is embedded in a text string..
FIND the # sign is only the beginning of the solution. But how do I go
from there ?
There is always a space between the last digit and the following
description text.
CO#002 Description text... Expected extraction : 2
CO# 005 xxxxxxxx... Expected extraction : 5
CO#0007 yyyyy... ... Expected extraction : 7
CO # 010 zzz.......... Expected extraction : 10
Help welcomed.

Celeste

I think I'd use the split function a couple of times inside a user defined
function. Try something like:
____________________________

Function MyExtract(strIn As String) As Long
arrayA = Split(strIn, "#")
arrayB = Split(LTrim(arrayA(1)), " ")
MyExtract = CLng(arrayB(0))
End Function
_______________________________

No need for the intermediate 'array' variables... you can combine it all
into one line:

Function MyExtract(strIn As String) As Long
MyExtract = CLng(Split(LTrim(Split(strIn, "#")(1)))(0))
End Function

Rick