Text parsing - Extracting data from inconsistent data entry format.

U

u473

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
 
S

Steve Yandl

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
 
R

Rick Rothstein \(MVP - VB\)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top