Replace Function with Left function

[Khalil Handal]

Hi to All,

I have an Input mask of text field with size 19 characters as follows:

000.###.###" ">CCCC<aCaa;_



I want to remove spaces in the first 14 characters and keep the rest (in case the user used space).

In the after update event of the control on the form, I used the following line of code:
Me.txtCallNumber = Replace(Left(strCallNumber, 14), " ", "")
This code is not working, it removes the space from ALL the string not only the first 14 characters.

Any suggestions?

Khalil

[Micron]

You're telling it to take the first 14 chars as a group and remove all the spaces. What you're left with should be nothing because you've dropped the remainder. But you don't indicate that's the case. What do you get as a result?

EDIT
Should clarify that would be the case if the first 14 were spaces. Not sure what your input is. I asked for an output example, but you should provide the input for it as well as I get varying results depending on what I feed it.

[Khalil Handal]

You're telling it to take the first 14 chars as a group and remove all the spaces. What you're left with should be nothing because you've dropped the remainder. But you don't indicate that's the case. What do you get as a result?

EDIT
Should clarify that would be the case if the first 14 were spaces. Not sure what your input is. I asked for an output example, but you should provide the input for it as well as I get varying results depending on what I feed it.

Here is a sample:
input output how it should be
26 72 ADC 4 2672ADC4 2672ADC 4

In other words it should keep spaces after the alphabetic part (the letters) and remove spaces before the letters only

[pbaldy]

How about:

?Replace(Left("26 72 ADC 4 2672ADC4 2672ADC 4", 14), " ", "") & mid("26 72 ADC 4 2672ADC4 2672ADC 4", 15)
2672ADC42672ADC4 2672ADC 4

[Micron]

There's a function that returns a number set, stopping as soon as it encounters text. I'm out now and can't research it. Thinking using Replace on that function's return would be simpler and more dynamic.

[pbaldy]

Sounds like Val():

?val("123abc")
123

but the first 14 characters in the example have alpha characters among them.

[Khalil Handal]

Sounds like Val():

?val("123abc")
123

but the first 14 characters in the example have alpha characters among them.

I tried the following:
Me.txtCallNumber = Replace(Left(strCallNumber, 14), " ", "") & Mid(strCallNumber, 15)

My input:
262 74 RTC 7

the output was:
26274RTC7
The must be a space after the letter C.

Thanks

[Micron]

Sounds like Val():
?val("123abc")
123
but the first 14 characters in the example have alpha characters among them.

it should keep spaces after the alphabetic part (the letters) and remove spaces before the letters only

Then I guess I don't understand the requirement based on that, as the sample "26 72 ADC 4" is only 11 characters long - including spaces.

When I ask for sample inputs and outputs, I always hope for more than just one so that I can detect patterns or get clarification when the situation seems to differ from that which was stated. It's getting harder for me to not get frustrated by such minimal inputs/outputs.

Regardless, Val() it was, but is probably of no use anyway.

[pbaldy]

I tried the following:
Me.txtCallNumber = Replace(Left(strCallNumber, 14), " ", "") & Mid(strCallNumber, 15)

My input:
262 74 RTC 7

the output was:
26274RTC7
The must be a space after the letter C.

Thanks

That's within the first 14 characters and you said "I want to remove spaces in the first 14 characters and keep the rest".

[Khalil Handal]

That's within the first 14 characters and you said "I want to remove spaces in the first 14 characters and keep the rest".

The string length is 19 characters; The user might enter less number of characters, say 6 or 10 or 19.
The first part of the string is numbers only that might include spaces followed by 3 or 4 letters. After the letters numbers might be entered with spaces. I want these spaces to be kepts.
Here are more sample for different cases:

User input: 272 TOM 4-5
output: 272TOM4-5
how it should be: 272TOM 4-5

User input: 27265 SAM 33
output: 27265SAM33
How it should be: 27265SAM33

User input: 168654359EPKH 6 8
Output: 168654359EPKH68
Requested: 168654359EPKH 6 8


These outputs are based on the code I am using now which I mentioned previously.
I hope this clarify it.

Khalil

[pbaldy]

Ah, a moving target. You were very specific about 14 characters earlier. The only way I can think of is a custom function that loops through the characters and determines the last alpha character. Then using that use a formula like earlier but with the 14 replaced by the value found previously as the last alpha character.

And why no space here before the 33?

How it should be: 27265SAM33

[Micron]

That will have to be more robust than I think you're thinking. There are spaces before the 6 an 8, both of which remain in the example.

[pbaldy]

That will have to be more robust than I think you're thinking. There are spaces before the 6 an 8, not of which remain in the example.

Is this directed at me? The function I'm envisioning would handle that, the formula supplied earlier was intended for the "14" restriction initially mentioned.

[Micron]

Yes, it was but upon review, I'd say you are right in what you said.

[orange]

@Khalil

I suggest you confirm your requirement since it seems to be moving as others have suggested.
Test, confirm and post a few examples of the revised requirement.
You might even describe what these text strings represent and why the format is so critical.