Link to home
Create AccountLog in
Avatar of Candace Hagood
Candace HagoodFlag for United States of America

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!
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you clarify which part of the string you need to keep?

RIGHT function returns the number of characters from the right hand end, your question then says you need to remove everything at the end.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi,
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")

Open in new window


Then get substring till that specific character like
S0=Mid(DimStr,1,P0)

Open in new window



Assuming your string is in cell A2, then you may try this...

=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2)))

Open in new window

@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 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