Is SPLIT() the way to go for this? 1

[Perilous1]

I am trying to take a string like this: 3; 8; 11; 19; 22; 27;

And remove 19; from the string and piece it back together so it then reads: 3; 8; 11; 22; 27;

I have been dinking around with using Split() to do this with mixed results and I am just curious if maybe there is a better option.

 

[mintjulep]

If your requirement is really as simple as stated then SUBSTITUTE() would be simpler.

 

[Perilous1]

I considered SUBSTITUTE but the string won't always be the same. Also, I will want to use the snippet of removed data elsewhere.

 

[Andrzejek]

Dim s As String
s = "3; 8; 11; 19; 22; 27;"

s = Replace(s, " 19;", "")
Debug.Print s

Unless there is more to your requirements that you stated in your post, and I suspect there is...
Like, how do you (or better yet: your program) know that the piece you want to deal with is 19; ?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[strongm]

> the string won't always be the same

Ok, so you need to be more explicit in your requirements, then.

 

[Perilous1]

I feel that I was explicit in that I said "a string like this" not "this string here".

Be that as it may, I believe I can use Andrzejek's method with an added overflow protection.

Thank you.

 

[strongm]

>in that I said "a string like this"

"Like this" in what sense? The same number of items? Each item within the same numerical range as the example string? Always the 4th item that needs to be removed? Always in ascending order?

e.g is

2; 3; 16; 13; 19; 4571;

legitimate, and what are we supposed to remove from it (i.e. Andrzejek's question about how you 'know that the piece you want to deal with is 19;')?

(BTW, if it happens that you already know the item you want to remove then I'm not quite sure I understand your "Also, I will want to use the snippet of removed data elsewhere." - you already have that data ...)

Also, not quite sure why you'd considered using SUBSTITUTE(), since that is an Excel function, and not a VB5/6 function.

 

[Perilous1]

Then you might want to take that up with mintjulep, as he/she is the one that suggested using it.

 

[MajP]

Huh?

 

[strongm]

I considered SUBSTITUTE but the string won't always be the same

 

[Perilous1]

Right.. Which was in response to:

mintjulep (TechnicalUser)24 Jul 17 18:57
If your requirement is really as simple as stated then SUBSTITUTE() would be simpler.

.. meaning I looked at SUBSTITUTE() but found it to be the wrong way to go for what I wanted to do.

 

[strongm]

Fine. But that's not what you said.