Trim everything before a character

[urobee]

Hy,

I have a text looks like:

"abcdef | 123456 | xyz"

I need to show only the last few character after the last " | " character, (delete everything before the last " | " and the pipe character too)
so in this case i need: "xyz" (the number of the characters is different all the time)

Oh, and i need to do this with VBA, not with formula.

Thanks for your help!

 

[Haluk]

If the text is cell A2, the below code will return the desired result.

MsgBox Evaluate("=TRIM(RIGHT(SUBSTITUTE(A2,""|"",REPT("" "",99)),99))")

 

[urobee]

If the text is cell A2, the below code will return the desired result.

MsgBox Evaluate("=TRIM(RIGHT(SUBSTITUTE(A2,""|"",REPT("" "",99)),99))")

Thanks, it works fine on a sheet if i use it like a formula without the "msgBox" part.
But i want to use it on an userform textbox to show the result. When i tried to use the code i got this error message: "Argument not optional".
The original text i want to trim is located in a combobox, so the code looks like this:

Trim(Right(Substitute(comboL.Value, "|", Rept(" ", 99)), 99))

I deleted some " character because i got "Invalid character" error when i use four " character.

 

[hiker95]

urobee,

If A1 contains abcdef | 123456 | xyz

Then in B1 try =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Oh, and i need to do this with VBA, not with formula.

Sorry, I missed that part.

 

[Rick Rothstein]

Thanks, it works fine on a sheet if i use it like a formula without the "msgBox" part.
But i want to use it on an userform textbox to show the result. When i tried to use the code i got this error message: "Argument not optional".
The original text i want to trim is located in a combobox, so the code looks like this:

Trim(Right(Substitute(comboL.Value, "|", Rept(" ", 99)), 99))

I deleted some " character because i got "Invalid character" error when i use four " character.

Substitute and Rept are not VBA functions, rather, they are Excel worksheet functions, so you need to use the Evaluate function in order for your VBA code to use them. However, here is a VBA only code line that will return the value you want...

[table="width: 500"]
[tr]
	[td]MsgBox Trim(Mid(ComboL.Value, InStrRev(ComboL.Value, "|") + 1))[/td]
[/tr]
[/table]

 

[urobee]

urobee,

If A1 contains abcdef | 123456 | xyz

Then in B1 try =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))




Sorry, I missed that part.

I tried to modify your original code and it works right now
Here is the modified code:

Trim(Right(WorksheetFunction.Substitute([COLOR=#333333]comboL.Value[/COLOR], "|", WorksheetFunction.Rept(" ", 99)), 99))

Thanks for your help!

 

[urobee]

Substitute and Rept are not VBA functions, rather, they are Excel worksheet functions, so you need to use the Evaluate function in order for your VBA code to use them. However, here is a VBA only code line that will return the value you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]MsgBox Trim(Mid(ComboL.Value, InStrRev(ComboL.Value, "|") + 1))[/TD]
[/TR]
</tbody>[/TABLE]

wow, what a good solution, thanks you too!

 

[hiker95]

urobee,

You are very welcome. Glad we could help.

And, come back anytime.

 

[Haluk]

Another alternative is;

MsgBox Split(ComboL.Value, "|")(2)

.

 

[Rick Rothstein]

Another alternative is;

MsgBox Split(ComboL.Value, "|")(2)

Your alternative will only work if there are always exactly three delimited fields, otherwise using a hard-coded 2 would not work (the OP's use of the phrase "everything before the last ' | '" seems to suggest otherwise). Also, you would want to Trim the array element otherwise you would have a leading space whenever there was a space following the pipe symbol. The more general way to use Split for this would be...

TempArr = Split(ComboL.Value, "|")
MsgBox Trim(TempArr(UBound(TempArr)))