Solved

MID formula error?

Posted on 2011-02-28
10
470 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:squirrelzan
  • 4
  • 3
  • 3
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:squirrelzan
ID: 34999183
Yes
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 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

by:squirrelzan
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

by:squirrelzan
ID: 34999242
I mean T3....
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 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

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

Kevin
0
 
LVL 18

Expert Comment

by:Cluskitt
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

by:squirrelzan
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

by:Cluskitt
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now