You need to sign in to do that
Don't have an account?
Function CASE formula
Hello,
I am trying to use a function CASE formula which will check for letters and replace them with a 0, add a leading 0 if only 6 characters and removes the dash.
ie: 81A-002 would change to 0810002
Please see formula below. I have added the SQL equivalent below as well.
CASE(ISNUMBER(MID('0' + SUBSTITUTE(Name, '-', ''), 5, 1)),1,
('0' + SUBSTITUTE(Name, '-', '')),0,MID('0' + SUBSTITUTE(Name, '-', ''), 1, 4) + '0' + MID('0' + SUBSTITUTE(Name, '-', ''), 6, 2))
Syntax Error: Incorrect number of parameters for function 'CASE()'. Expected 4, received 5
CASE WHEN ISNUMERIC(SUBSTRING('0' + REPLACE(Name, '-', ''), 5, 1)) = 1 THEN '0' + REPLACE(Name, '-', '')
ELSE SUBSTRING('0' + REPLACE(Name, '-', ''), 1, 4) + '0' + SUBSTRING('0' + REPLACE(Name, '-', ''), 6, 2)
END
Thank you!
This is not the most elegant solution, but it seems to work:
Basically, it first converts any characters to lower case (because SUBSTITUTE() is case sensitive), and then uses nested SUBSTITUTE() functions to replace any "a" with "0", then "b", then "c", all the way down to "z". Then it removes the hyphen.
It will work even if there are multiple letters (e.g. 81A-0A2 or 96B-0H1)
I had to put the "z" SUBSTITUTE() call all on the same line because all the indenting spaces put the formula over the 3,900 character limit :-)
All Answers
I don't think you need a CASE() function here -- I haven't had a chance to dig into the subsitution part for the letter but I started to stub it out like this:
Is the letter always in the same position, preceding the hyphen?
LPAD(
SUBSTITUTE(
Name,
"-",
""
),
7,
"0"
)
So far this is working except it's not replacing the letter with a zero. And to answer your question, the letter is not always in the same place.
Thanks for your help! Can you advise on the letter problem we are having?
This is not the most elegant solution, but it seems to work:
Basically, it first converts any characters to lower case (because SUBSTITUTE() is case sensitive), and then uses nested SUBSTITUTE() functions to replace any "a" with "0", then "b", then "c", all the way down to "z". Then it removes the hyphen.
It will work even if there are multiple letters (e.g. 81A-0A2 or 96B-0H1)
I had to put the "z" SUBSTITUTE() call all on the same line because all the indenting spaces put the formula over the 3,900 character limit :-)
At first, thought you were being a smart alec. Worked like a charm! I marked it as such.
Thank you!