[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel query question

Posted on 2014-07-11
6
Medium Priority
?
294 Views
Last Modified: 2014-07-11
I have written an excel function like below:
="WHEN mynumber >= '" & D65 & "' AND mynumber <= '" & E65 & "' THEN '" & A65 & " " & B65 & " " & C65 & "'"

The above mostly works the way I want. But if there value of C33 is null, it ends in an empty blank character at the end of the calculated value.
Could you suggest how I could get rid of the empty character. I trid TRIM function but could not make that work.

Thanks a lot.
0
Comment
Question by:toooki
  • 3
  • 3
6 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40191236
* You could use my Better Concatenate function.
http:A_7811-A-Better-Concatenate-Function.html

* You will probably need to use an IF() function to check the existence of a cell and only prepend the space character if there is something to concatenate.

* If C33 is the only cell that might be empty, you could Trim the result.
="WHEN mynumber BETWEEN '" & D33 & "' AND '" & E33 & "' THEN '" & Trim(A33 & " " & B33 & " " & C33 & "'")

Open in new window

0
 

Author Comment

by:toooki
ID: 40191336
Thank you. I will look into the Better Concatenate function. But for now I tried:
="WHEN mynumber BETWEEN '" & D33 & "' AND '" & E33 & "' THEN '" & Trim(A33 & " " & B33 & " " & C33 & "'")
But this did not eliminate the last character coming out of null E33 value.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40191349
Trim each of those cells as well.
="WHEN mynumber BETWEEN '" & Trim(D33) & "' AND '" & Trim(E33) & "' THEN '" & Trim(A33 & " " & B33 & " " & C33 & "'")

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:toooki
ID: 40191413
Thank you. But sorry this does not remove the last blank space "before" the ' character (single quote character) at the end.
Because the Trim(A33 & " " & B33 & " " & C33 & "'") is not trimming before the ' character.
I tried to modify the above to bring the 1st bracket before the ' character but I get syntax error.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40191419
Sorry.  Misplaced parenthesis for that original Trim function.
="WHEN mynumber BETWEEN '" & Trim(D33) & "' AND '" & Trim(E33) & "' THEN '" & Trim(A33 & " " & B33 & " " & C33) & "'"

Open in new window

0
 

Author Comment

by:toooki
ID: 40191457
Works! Many thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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