Return the characters after Nth character in a Cell



Return the characters after the nth ","|"."

Use of SUBSTITUTE function when you are replacing text based on its content.

SUBSTITUTE find and replaces your old text with new text in a text string. 


SUBSTITUTE(text, old_text, new_text, [instance_number])

The SUBSTITUTE function syntax has the following arguments
Text  Required. The text or the reference to a cell containing text for which you want to substitute characters.
Old text  Required. The text you want to replace.
New text  Required. The text you want to replace old text with.
Instance number  Optional. Specifies which occurrence of old text you want to replace with new text. If you specify instance number, only that instance of old text is replaced. Otherwise, every occurrence of old text in text is changed to new text.

REPT :- Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.


REPT(text, numbertimes)

Text  Required. The text you want to repeat.
Number times  Required. A positive number specifying the number of times to repeat text.

TRIM :-


TRIM(text)

Text  Required. The text from which you want spaces removed.




Return the characters after 10th character 


=TRIM(RIGHT(SUBSTITUTE(G2,"|",REPT(" ",11255),10),11255))
=TRIM(RIGHT(SUBSTITUTE(G3,".",REPT(" ",11255),10),11255))
=TRIM(RIGHT(SUBSTITUTE(G4,",",REPT(" ",11255),10),11255))

Comments

Post a Comment

jeetexceltips@gmail.com

Popular posts from this blog

50 Excel VBA Oral Interview Questions

Ques 01. What is the difference between ByVal and ByRef and which is default ? Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure. ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained. ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference. Ques 02. What is the meaning of Option Explicit and Option Base? Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0. Option Base - When an array is declared by d...
Read more