How do I remove character from text

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

Guest

I need to remove "." from A.008733 and to select the numbers to the left of
"/" in G089308/A.
Thanks
 
For the first problem, use the Replace function. For the second problem, use
a combination of the Left and InStr functions. Here are some examples using
the VBA Immediate window. The help topics for these functions will provide
more details.

? Replace("A.008733", ".", "")
A008733

? Left$("G089308/A", InStr(1, "G089308/A", "/")-1)
G089308
 
The easy one:
Replace("A.008733",".","")

The problem one:
Val(Mid(Left("G089308/A",Instr(1,"G089308/A", "/")-1),2))
or
Mid(Left("G089308/A",Instr(1,"G089308/A", "/")-1),2)

Val gives you the number which excludes the leading 0. The second one
without Val gives you the numerical characters including the leading 0. There
is a difference between "numbers" and "numerical characters".

The above only works if there is a single alphabetical character at the
start of the string.
 
hatemma said:
I need to remove "." from A.008733 and to select the numbers to the left of
"/" in G089308/A.
Thanks
Click to expand...
~~~~~~~~~~~~~~
If the numbers are always this identical format (8 keystrokes with the
"." in the 2nd place) you can use this expression:

left(YourFieldName,1)&right(YourFieldName,6)

and if the 2nd example is always that same format, (9 keystrokes with
the "/" in the 7th position) you can use:

left(YourFieldName,7)

Now, if these are all kept in the same field, you'll have to filter the
operation to avoid changing the wrong entries. For instance:

create a column in your query for:

InStr([sampleno],".")

Use "2" (no quotes) to filter this expression and you'll get only those
records that have a period in the 2nd position. Once this works, you
can then safely run an update query that alters those records.

Likewise, use Instr to filter "/" in the 7th position before you make
that change.

Hope I understood your situation.

Betsy
 
Back
Top