MID formula error?

I am trying to do a MID formula that looks at entries in a column and brings the information into a single cell. However, if the entry starts with an alph character, it will bring that back, but if the entry start with a numeral, it doesn't.

I have attached a copy of my spreadsheet.

Any ideas?

Thank you in advance.
Book1.xlsx
Farah MohammedBuyerAsked:
Who is Participating?
 
CluskittConnect With a Mentor Commented:
That is because a number isn't larger than "". You have 2 options:

1- Force the column as text. When you insert a number it will be read as text.
2- Change T2>"" to OR(T2>"",T2>0). Same for the other ones. Basically, you're checking for a string or a value.

The best practice would be #1.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Is this what you want?

=MID(IF(T2<>"", " "&T2,"") & IF(T3<>""," / "&T3,"") & IF(T4<>""," / " &T4,"") & IF(T5<>""," / "&T5,""),1,15)

Kevin
0
 
Farah MohammedBuyerAuthor Commented:
Yes
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Farah MohammedBuyerAuthor Commented:
Trying # 1 above - I forced column T as text, but the formula (F2) still didn't bring in T2.

See attached.
Book1.xlsx
0
 
Farah MohammedBuyerAuthor Commented:
I mean T3....
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Dude, this works:

=MID(IF(T2<>"", " "&T2,"") & IF(T3<>""," / "&T3,"") & IF(T4<>""," / " &T4,"") & IF(T5<>""," / "&T5,""),1,15)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Don't mess around with cell formatting. The comparison operators override the formatting.

Kevin
0
 
CluskittCommented:
If you use <>"" that will work for everything except a NULL value, which isn't the same as "".
If you use >"" it will only work for strings.
0
 
Farah MohammedBuyerAuthor Commented:
I tried Kevin's formula above and it works.

Cluskitt - I don't think I need to worry about NULL values as the invoice number(s) will be manually entered.
0
 
CluskittCommented:
To get a NULL value in excel, you'd have to force it. So yes, you don't have to worry about it :)
0
All Courses

From novice to tech pro — start learning today.