Deleting characters from cells in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would one delete characters up to a specific criteria in cells with a
query. For example, you have a street addresses in the format of number then
street and you only want to the number portrion. Street number and street
both vary in character length.
Thanks.
 
How would one delete characters up to a specific criteria in cells with a
query. For example, you have a street addresses in the format of number then
street and you only want to the number portrion. Street number and street
both vary in character length.
Thanks.
Click to expand...

So you want to take an address like

3142 Maple St.

and permanently and irrevokably destroy the street name, converting
that field to

3142

Or what DO you want to do???

You can *DISPLAY* the substring up to the first blank with an
expression like

Left([Address], InStr([Address], " ") - 1)

or the remainder after the first blank with

Mid([Address], InStr([Address], " ") + 1)

The InStr function finds the position of the first blank, and the
Left() and Mid() functions extract substrings. These results can be
used as calculated fields in a query, or the function can be used in
an Update query to update new fields AddressNo and Street in your
table. Not sure exactly what you want, but here's some options!

John W. Vinson[MVP]
 
Thanks John,

You got it just right.

John Vinson said:
How would one delete characters up to a specific criteria in cells with a
query. For example, you have a street addresses in the format of number then
street and you only want to the number portrion. Street number and street
both vary in character length.
Thanks.
Click to expand...

So you want to take an address like

3142 Maple St.

and permanently and irrevokably destroy the street name, converting
that field to

3142

Or what DO you want to do???

You can *DISPLAY* the substring up to the first blank with an
expression like

Left([Address], InStr([Address], " ") - 1)

or the remainder after the first blank with

Mid([Address], InStr([Address], " ") + 1)

The InStr function finds the position of the first blank, and the
Left() and Mid() functions extract substrings. These results can be
used as calculated fields in a query, or the function can be used in
an Update query to update new fields AddressNo and Street in your
table. Not sure exactly what you want, but here's some options!

John W. Vinson[MVP]
Click to expand...
 
Is there anyway you could use two identifying characters rather than just one
for the InStr command? For example if you have both "+" and "-"
alternatively appearing in cells.

Thanks

John Vinson said:
How would one delete characters up to a specific criteria in cells with a
query. For example, you have a street addresses in the format of number then
street and you only want to the number portrion. Street number and street
both vary in character length.
Thanks.
Click to expand...

So you want to take an address like

3142 Maple St.

and permanently and irrevokably destroy the street name, converting
that field to

3142

Or what DO you want to do???

You can *DISPLAY* the substring up to the first blank with an
expression like

Left([Address], InStr([Address], " ") - 1)

or the remainder after the first blank with

Mid([Address], InStr([Address], " ") + 1)

The InStr function finds the position of the first blank, and the
Left() and Mid() functions extract substrings. These results can be
used as calculated fields in a query, or the function can be used in
an Update query to update new fields AddressNo and Street in your
table. Not sure exactly what you want, but here's some options!

John W. Vinson[MVP]
Click to expand...
 
Is there anyway you could use two identifying characters rather than just one
for the InStr command? For example if you have both "+" and "-"
alternatively appearing in cells.
Click to expand...

Not easily, especially if you have both a + and a - in the field.
Could you give an example of the string you're trying to parse, and
the desired result?

John W. Vinson[MVP]
 
Say I have a two entry column with GE+LG and SXY-SE. I would like to get GE
and SXY in a new column. Thanks for you advice.
 
I ended up doing it the long way, two update queries in a row that used your
earlier formula, one for + and one for -. I create a new column to be
updated and used the critera of + or minus respectively in the updates.
Thanks for your advice in any event.

CC
 
I ended up doing it the long way, two update queries in a row that used your
earlier formula, one for + and one for -. I create a new column to be
updated and used the critera of + or minus respectively in the updates.
Thanks for your advice in any event.
Click to expand...

That's probably what I would have recommended - glad you got it
running!

John W. Vinson[MVP]
 
Back
Top