All characters left of a number

[swaink]

Hi All

I wonder if you can assist me please

I am in the process of cleaning up some date (1.5m records) its a slow process but i'm getting there

However I have become stuck on a query for one field, the field should only contain names but in some instances I am seeing address details too

I have been running update queries to tidy up but I am not stuck as to how I can look foir all the characters to the left of a number

for example Mr Jon Do 43 terrace gardens I only want to see Mr Jon Do

I have tried this Expr1: Left([contact],(InStr(1,[contact],'1')-1)) but I wondered if there is anyway to get the left of any numeric value rather than do this one at a time

Any advice appreciated

All the best

Kevin

 

[Scrappy666]

Hi Kevin,

Give this a try:

=LEFT(A1,MIN(IFERROR(FIND(B1:B10,A1),""))-2)

This is an array formula, enter with CTRL + SHIFT + ENTER.

A1 is the cell with the text.
The range B1:B10 has the list of numbers from 0-9.

Let me know if you get stuck

Thanks,
Dan

 

[swaink]

Hi Dan

Thank you for the reply mate I do appreciate it

Unfortunately this works ok in Excel but I need to do the same sort of thing but in Access and thats where I am stuck

All the best

Kevin

 

[Joe4]

One way is to create a User Defined Function like this:

Function GetText(myEntry As Variant) As String


    Dim myLen As Long
    Dim i As Long
    
    myLen = Len(Nz(myEntry, ""))
    If myLen > 0 Then
        For i = 1 To myLen
            If IsNumeric(Mid(myEntry, i, 1)) Then
                Exit For
            End If
        Next i
        GetText = Trim(Left(myEntry, i - 1))
    Else
        GetText = ""
    End If
    
End Function

Then, just use it like any other Function in a calculated field, i.e.
NameOnly: GetText([contact])

 

[Scrappy666]

Ahhh sorry Kevin!

I was using the 'Zero Reply Posts' section and didn't notice the forum...

Hope Joe's solution works!!

 

[Joe4]

I was using the 'Zero Reply Posts' section and didn't notice the forum...

You be surprised how often that happens! I have seen three in the last week.

Since the forum is listed over to the right, it can be easy to miss.

 

[swaink]

Hi Guys

Thank you for your help

Joe this work great for me and will save a lot of time going forward

Dan no worries mate I had to double check I had posted it in the right forum as I don't usually use Access so still learning

Appreciated Joe thanks again

Kevin

 

[Joe4]

Your welcome!
Glad to help!

 

[swaink]

Hi Joe

Just out of interest should I ever need it how could the function be tweaked to to get everything to the right of and including the number i,e, 43 terrace gardens

All the best

Kevin

 

[Joe4]

swaink,

Why not try taking a stab at it? It will be a good test to see if you understand what the code is doing.
Once you see how it works, it should be pretty easy to make that modification.
Note, this is the line you most want to focus on:

GetText = Trim(Left(myEntry, i - 1))

See how you do and post back!