Find and Delete XX characters from Right

[ptmuldoon]

I have a column of names/text where I need to search and find a " - " minus symbol from the right, and then delete that symbol and all text to the right of that as well.

The minus symbol will exist more then once in the Cell, thus need to search for it from the right. Currently this data is in column G.


Can anyone help with that this morning?

[MarvinP]

Hi ptmuldoon,

What if you replaced all "-" characters in your string to "-"& "100 spaces". Then delete the last 100 characters from this new string. NOW - the trick is that if you TRIM() the string it will reduce all those extra spaces to a single space. This would be my approach. If you give us a few sample strings, we might build a formula for you.

[benishiryo]

hi ptmuldoon. try:
=LEFT(A1,FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

[Syrkrasi]

This will delete everything to the right of the "-": =LEFT(A1,LEN(A1)-FIND("-",A1))
This will delete everything to the left of the "-": =RIGHT(A1,LEN(A1)-FIND("-",A1))

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[ptmuldoon]

Guys,

Sorry for the delays in the response. Brutal work/files this week to analyze. But both macro and formula's above all work well. Now I can least pivot and do some analysis on customer names.

As an example, I was dealing with "CustomerName - New York", "CustomerName - Chicago", etc. Now I've eliminated the cities, etc. to get a common name.