Extracting data from String

[allredkj]

I need to perform an update query in access to a column in the same table which is currently null/blank. I'm trying to extract residential legal descriptions from a string. I would like to extract the 'Lot' and 'Block'. Below is a good example of my data:

BLK 1 LT 45
BLK 1 LT 46 ACS 0.3595
BLK 10/6666 LT 17
BLK 10/6666 LT18
BLK 11/6666 LOT 2 ACS 0.28
BLK A/7964 LT 1.1
BLK 1 LT 17A ACS 0.0427 CALC
BLK 4/3595 LT 3
BLK 128/76 1/2 LTS 1-3, 8-10
PT LOTS 5 & 6-55X48.5X59X81X100
BLK D/7391 LT 18
BLK 7 LT 60

I would like to see the following extracted for Lot:
45
46
17
18
2
1 or 1.1 is ok
17A
3
1 or 1-3 is ok
5 or 5 & 6 is ok
18
60

I would like to see the following for Block:
1
1
10
10
11
A
1
4
128
(null)
D
7

 

[MarkK]

You haven't actually asked a question. How much programming experience do you have? Writing code to parse this kind of thing is very tedious and prone to error.

 

[allredkj]

My question is that I need assistance with a formula to extract what I have written above. I am having a lot of difficulty with it so I have posted the question. It is definitely too technical for me. If it is too technical for this forum, please advise and disregard this request for assistance.

Respectfully,
Kelly

 

[MarkK]

I get a kick out of helping people who seem interested in programming because they think it's cool, because I think it's cool. Then I feel like I share a joy of learning with someone. But if you just want me to write code to your specifications, I don't do that for free.

So if you have a specific question, or show that you've already done some work but got stuck, that's something else.

See the difference? Hope that helps,

 

[allredkj]

Hi Lagbolt,

Thank you for your response. I love working with data but have not done it to this level in Access. Don't worry about this request, we can close it, I've decided it is much easier to do this extraction in Excel and then import the results/new column back into Access. Maybe I was going about it the wrong way. I definitely see your point but I'm just not as technical, I was trying to use the Instr in my update query but just couldn't get it right. I apologize for not posting a sample here. I appreciate your time and assistance.

Thanks,
Kelly

 

[MarkK]

Kelly, very gracious response. Thank you.
For parsing strings I love the VBA.Split() function, which splits a string into an array as delimited by a character that you can specify. So this code...

dim vWords as variant
vWords = Split("This is a test", " ")

...creates a four element array in the vWords variable that you can then enumerate like this...

dim i as integer
for i = 0 to ubound(vWords)
[COLOR="Green"]    'process a single word here[/COLOR]
    debug.print vWords(i)
next

...and process each individual part of your string.

Cheers,

 

[allredkj]

Thank you Lagbolt but I was looking for something to use in an update query. I do not know VB.