Candace Hagood
asked on
EXCEL remove characters in string from Right until a certain character
Hello,
I have an excel sheet with many rows of serial numbers. I need to perform a =RIGHT on the cells in one of the columns. The problem is the values vary in length. The one issue of consistency is that I need to remove everything at the end until the LAST dash. Any ideas appreciated.
Thank you!
I have an excel sheet with many rows of serial numbers. I need to perform a =RIGHT on the cells in one of the columns. The problem is the values vary in length. The one issue of consistency is that I need to remove everything at the end until the LAST dash. Any ideas appreciated.
Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
You can use instr function to get position of specific character
Then get substring till that specific character like
You can use instr function to get position of specific character
DIM S0 as String, DimStr as String
DimStr="YourBString"
DIM P0 as Integer
P0=instr(DimStr,"B")
Then get substring till that specific character like
S0=Mid(DimStr,1,P0)
Assuming your string is in cell A2, then you may try this...
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2)))
@Neeraj - I knew there was a simpler way with substitute; that works when you want to keep the text after the last "delimiter" character but need confirmation from Author as to which part they require to remain.
I had originally formulated mine using the TRIM and REPT functions but just looked way too complicated.
I had originally formulated mine using the TRIM and REPT functions but just looked way too complicated.
I believe it is an easy thing to do in Power Query. Do you have some examples? Below is an example of Excel's Power Query.
EE.xlsx
EE.xlsx
RIGHT function returns the number of characters from the right hand end, your question then says you need to remove everything at the end.