Seperate letters and numbers 12a, 3b etc (1 Viewer)

[jomuir]

I want to split data in one field into 2, so 12a would be 12 & a or 1a would be 1 & a, I have managed this where the format is split by / but not sure how to split based on letters and numbers?

How can I separate this data?

12a
14b
1e
5f
etc

I have created a query that splits data into 2 based on “/” so:-

12/1 would be displayed into 2 new fields as 12 & 1

Numb: Left([Stock Transfer].[ADDRESS],Len([Stock Transfer].[ADDRESS])-(Len([Stock Transfer].[ADDRESS])-(InStr([Stock Transfer].[ADDRESS],"/")-1)))

Flat: Right([Stock Transfer].[HOUSE_NO],(Len([Stock Transfer].[HOUSE_NO])-(InStr([Stock Transfer].[HOUSE_NO],"/"))))

 

[Galaxiom]

Use the IsNumeric function.

 

[jomuir]

Sorry, can you please explain a bit more, I do not understand how I can use IsNumeric to split 13a into 13 & a

If I use t1: IsNumeric([HOUSE_NO]) I just get a True or False (-1 & 0)

I want to split the numbers and the letters up
1a
1b
1c
2a
2b
Would become 2 columns:-
1
1
1
2
2

and
a
b
c
a
b
--------------------
Figured it out.....

IIf(Len([Stock Transfer_UPRN]![HOUSE_NO])=2,Left([Stock Transfer_UPRN]![HOUSE_NO],1),IIf(Len([Stock Transfer_UPRN]![HOUSE_NO])=3,Left([Stock Transfer_UPRN]![HOUSE_NO],2),IIf(Len([Stock Transfer_UPRN]![HOUSE_NO])=4,Left([Stock Transfer_UPRN]![HOUSE_NO],3),"")))

Right([Stock Transfer_UPRN]![HOUSE_NO],1),"")

 

[Galaxiom]

While you solution works for the limited range it isn't very general and would obviously get out of hand as the range of numbers increased. Indeed it would not work on 123a.

The IsNumeric function can be used to test character by character (using the Mid function) in a loop through a string. With a bit of creativity it can separate any pattern of alphanumeric combinations.

 

[vbaInet]

Maybe something like:

Val([COLOR=Blue][B][Field][/B][/COLOR]) & " & " & Mid([COLOR=Blue][B][Field][/B][/COLOR], Len(Val([B][COLOR=Blue][Field][/COLOR][/B]) & vbNullString) + 1)

But you would have to test if the first character is numeric, so the final piece:

IIf(IsNumeric(Left([COLOR=Red][B][Field][/B][/COLOR], 1))[COLOR=Red][B],[/B][/COLOR] Val([COLOR=Blue][B][Field][/B][/COLOR]) & " & " & Mid([COLOR=Blue][B][Field][/B][/COLOR], Len(Val([B][COLOR=Blue][Field][/COLOR][/B]) & vbNullString) + 1)[COLOR=Red][B],[/B][/COLOR] [COLOR=DarkRed][B][Field][/B][/COLOR])

 

[jomuir]

Thank you!!

That is a great help, in my query I am always asked to enter a value for vbNullString, so I am not really sure why this is there....checking for nulls from what I have read.

I am looking up what the other functions are doing - thanks!

 

[vbaInet]

Oops... forgot those aren't used in queries. Change vbNullString to ""