Unparseable???
I am trying to figure out why my formula keeps saying #UNPARSEABLE
I am trying to take the first four digits of a project number and join it with 2 digits that are the suffix to create a new project number. Please help! Here is the formula:
=JOIN(MID([Project Number]1,1,4):[Project Number Suffix]@row)
Best Answer

The JOIN function only works if the two information columns are together as it asks for a range. If they aren't together you can join them manually with a plus sign. If they are numbers they will add up so we can place a set of quotation marks between two plus signs
=MID([Project Number]1,1,4)+""+[Project Number Suffix]@row
Try this formula.
If you'd like for there to be a delimiter between the two values you can add it between the Quotation Marks like this:
=MID([Project Number]1,1,4)+""+[Project Number Suffix]@row
=MID([Project Number]1,1,4)+"/"+[Project Number Suffix]@row
Hope this helps!
Answers

Hi Sam Sasin,
When a formula says #UNPARSEABLE its a syntax problem. Reviewing your formula, I guess that the error is the colon you have in the middle.
=JOIN(MID([Project Number]1,1,4):[Project Number Suffix]@row) try replacing the colon with a comma,
=JOIN(MID([Project Number]1,1,4),[Project Number Suffix]@row)
Let me know if this worked!

I changed the colon to a comma like you said and it did the MID function but did not add the last two numbers on with the JOIN function, how can I get the JOIN function to work?

The JOIN function only works if the two information columns are together as it asks for a range. If they aren't together you can join them manually with a plus sign. If they are numbers they will add up so we can place a set of quotation marks between two plus signs
=MID([Project Number]1,1,4)+""+[Project Number Suffix]@row
Try this formula.
If you'd like for there to be a delimiter between the two values you can add it between the Quotation Marks like this:
=MID([Project Number]1,1,4)+""+[Project Number Suffix]@row
=MID([Project Number]1,1,4)+"/"+[Project Number Suffix]@row
Hope this helps!

That does make a lot of sense, I tried your first formula because I do not need a delimiter, but now this formula is saying it is #UNPARSEABLE

I have edited the last comment as I accidentally left a parenthesis, please try copy and pasting it again or just removing the last parenthesis from your formula.

That worked! Thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!