How can I remove any text after number

[zezo2021]

Hello friends;
How can I remove any text after number for example
jon 15-5-77 mq

I want to remove mq

become
jon 15-5-77

Thank you

 

[theDBguy]

For that specific sample data, you could try:

Left([TextField], InStrRev([TextField], " ")-1)

 

[zezo2021]

For that specific sample data, you could try:

Left([TextField], InStrRev([TextField], " ")-1)

Thank you I will test your solution immediately

Thank you for fast reply

 

[plog]

With what you've given us the solution is to loop through your input string, character by character to find the position of the last number. Then you can use Mid to get the substring up until that point. Here's some psuedo code:

int_LastNumberPosition=0

for i=1 to i=YourString.Length
  if IsNumeric(YourString[i]) then int_LastNumberPosition=i

ReturnString=Mid(YourString, 1, int_LastNumberPosition

IsNumeric - https://www.techonthenet.com/access/functions/advanced/isnumeric.php
Mid - https://www.techonthenet.com/access/functions/string/mid.php
For Loop - https://www.techonthenet.com/access/functions/advanced/for_next.php

 

[zezo2021]

Thank you I will test your solution immediately

Thank you for fast reply

I need to edit
I want to
Jak S. 080605-DECEASED

I want remove any character or number after the six digit 080605 any six digit 080609, 010203
the string will be
Jak S. 090701


we can say:
Expression
Search for any six digit if you found any six digit remove anything after them

 

[theDBguy]

I need to edit
I want to
Jak S. 080605-DECEASED

I want remove any character or number after the six digit 080605 any six digit 080609, 010203
the string will be
Jak S. 090701

Okay, giving us data samples one at a time will probably not going to be efficient. We can give you a solution for this one, but you might come back again with a different sample later on.

How about giving us enough samples and a clear description of your rules, so we can formulate a more permanent solution?

 

[zezo2021]

Okay, giving us data samples one at a time will probably not going to be efficient. We can give you a solution for this one, but you might come back again with a different sample later on.

How about giving us enough samples and a clear description of your rules, so we can formulate a more permanent solution?

last sample

 

[theDBguy]

last sample

Try this:

Left([TextField], InStrRev([TextField], "-") -1)

 

[zezo2021]

Try this:

Left([TextField], InStrRev([TextField], "-") -1)

this scrapped data
my contain - or space
how can i combine both in one expression

or can you give me solution depend on number of digit
if six digit remove anything after

 

[theDBguy]

this scrapped data
my contain - or space
how can i combine both in one expression

or can you give me solution depend on number of digit
if six digit remove anything after

One way is to use an IIf() statement to use the correct code.

However, I have a feeling you might come back again later on and either say there's an additional character you want to check or the text might contain a combination of those characters.

 

[NauticalGent]

Regular Expressions is your answer. Unfortunately, my skill with them is not good enough to help you.

@arnelgp is our resident expert here, maybe he will weigh in.

 

[Pat Hartman]

I don't understand what was wrong with theDBguy's solution. What did you start with? What did you end up whith? What was YOUR code?