Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# MID formula error?

Posted on 2011-02-28
Medium Priority
477 Views
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?

Book1.xlsx
0
Question by:Farah Mohammed
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 3

LVL 81

Expert Comment

ID: 34999173
Is this what you want?

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

Kevin
0

Author Comment

ID: 34999183
Yes
0

LVL 18

Accepted Solution

Cluskitt earned 1000 total points
ID: 34999195
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

Author Comment

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

See attached.
Book1.xlsx
0

Author Comment

ID: 34999242
I mean T3....
0

LVL 81

Assisted Solution

zorvek (Kevin Jones) earned 1000 total points
ID: 34999258
Dude, this works:

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

Kevin
0

LVL 81

Expert Comment

ID: 34999261
Don't mess around with cell formatting. The comparison operators override the formatting.

Kevin
0

LVL 18

Expert Comment

ID: 34999280
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

Author Comment

ID: 34999350
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

LVL 18

Expert Comment

ID: 34999366
To get a NULL value in excel, you'd have to force it. So yes, you don't have to worry about it :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month9 days, left to enroll