Detecting Blanks and Non Text Characters.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help

=IF( E61="Cable",Q61,IF( E61
="Terminal",IF(ISBLANK(Q61),"",CONCATENATE($R$17," ",Q61)),""))

The above function concatenates the contents of R17 and Q61 in R61, this
works but some cells in Q61 contain blank spaces, therefore giving me
unwanted text in R61.

Is there any way I can perform the above function but with valid characters
only, ie ""a" to "z" and 1 to 9

Thanks
Alec
 
You could change this part

IF(ISBLANK(Q61),"",

to

IF(LEN(TRIM(Q61))=0,"",
 
I think that the problem is that ISBLANK gives false for cells with formulas
resulting in a "", if you dont have one which would result in a " " you cound
change to
=IF( E61="Cable",Q61,IF( E61
="Terminal",IF(countblank(Q61)=1,"",CONCATENATE($R$17," ",Q61)),""))
if there is a chance of a " " then
=IF( E61="Cable",Q61,IF( E61
="Terminal",IF(countblank(trim(Q61))=1,"",CONCATENATE($R$17," ",Q61)),""))
 
Thanks for your help

Alec



bj said:
I think that the problem is that ISBLANK gives false for cells with formulas
resulting in a "", if you dont have one which would result in a " " you cound
change to
=IF( E61="Cable",Q61,IF( E61
="Terminal",IF(countblank(Q61)=1,"",CONCATENATE($R$17," ",Q61)),""))
if there is a chance of a " " then
=IF( E61="Cable",Q61,IF( E61
="Terminal",IF(countblank(trim(Q61))=1,"",CONCATENATE($R$17," ",Q61)),""))
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Detecting Formula Result 2
Concatenate and remove blanks 3
Populate cell with multiple non-blank text values 3
Search for specific text within last non blank cell in a range 2
Formula with text and numerics 3
Remove Blank and Non Data Rows 3
count of tab characters in a single cell 5
VBA Code and Summing Next Non-Blank Cell in Column 9

Back
Top