0

I'm not sure how to title this question.

I have a complicated challenge that I'm not sure how to approach in excel.

I have a column with long variable length strings, example 

c68d7a51-5cef-4a5c-b417-b2751fe9754b  
c68d7a51-5cef-4a5c-b417-b2751fe9754b-0  
c68d7a51-5cef-4a5c-b417-b2751fe9754b-1
09573f-1780c84f-e5ff-85821a-6236e0399d
09573f-1780c84f-e5ff-85821a-6236e0399d-10
09573f-1780c84f-e5ff-85821a-6236e0399d-123

Where for the ones that end in a "-***" I need to have a new column which returns the string without that ending RIGHT substring else null. The ending substring is anywhere from 1 to 3 characters long after the "-" specific character. I also need to note based on the comments, that I can't work from the left because the strings change in length and in "-" placement. The only constant is that some of these strings have child substring values from 1 to 3 digitals after the last "-".

What I have so far for an outline looks like this 

=IF(RIGHT(A1,4) contains "-", RIGHT(A1, LEN(A1) - number_of_chars_to_remove), else null.

If you don't mind, how do I go about accomplishing this?

I appreciate your help, thank you!

Improve this question
4
  • is it always 4 - then the - you want to remove?
    – Scott Craner
    Sep 18, 2017 at 19:59
  • Hi Scott, 4 is the maximum length of the ending substring from the right of the whole string including the "-" divider. But sometimes the string's last 4 will not contain a "-" which means return null because everything is ok. But when the last 4 contain a "-" I need to remove the "-" and everything after it for a new column. Where the characters after "-" (in the last 4) will be either 1, 2, or 3 digits.
    – Rocket Spaceman
    Sep 18, 2017 at 20:03
  • That is not what I asked. Right now you have a pattern that can be exploited. You have 4 or 5 "-" and you want the string before the 5th "-" or null if no 5th "-". So I ask again, are there always 4 "-" with the 5th "-" being the decider in your strings?
    – Scott Craner
    Sep 18, 2017 at 20:05
  • Oh, in the left of the string? yea misunderstood you, no left of the string is random in that it changes in both length and number of dashes, which is why I can't work from the left.
    – Rocket Spaceman
    Sep 18, 2017 at 20:08

2 Answers 2

Reset to default
1

Use this:

=IF(ISNUMBER(FIND("-",RIGHT(A1,4))),LEFT(A1,FIND("}}}",SUBSTITUTE(A1,"-","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1),"")

It first test if there is a - in the last four, if so it finds that last - and changes it to }}}. Then it finds that and makes that the end of the LEFT equation.

enter image description here

Improve this answer
1
  • You're a magician! thank you so much! You saved my bacon!
    – Rocket Spaceman
    Sep 18, 2017 at 20:15
0

It seems to me, based on your sample data, that you could count the string and if it has 5 dashes, return the first 38 characters, otherwise, return NULL. That would be a lot simpler. 38 is significant as its the length of the string up to the fifth dash.

Improve this answer
1
  • Good suggestion, the only constant though I have in the dashes is either dash in the last 4 or not. Because the string - locations to the left of that will all be random.. It's really messy I know.. but that's why I'm trying to work from the right instead of the left. because the right is the most constant outcome I have to work with in knowing that the last 4 will either have a dash or not. and if it does have a dash, it will only have 1 to 3 digits after it.
    – Rocket Spaceman
    Sep 18, 2017 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.