Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-06-2019, 12:26 PM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default How make IF Formula return a numbers joined with a letter

Hi Experts,



Almost 3 years have passed since I found nice features of MS Word to use Excel calculations in tables including logical functions. But seems they work only with numbers.
In the table when using IF function is it possible to have returned set of numbers joined with letter like below:
Something like:
=IF(A1=330,"39x33x15",IF(A1=250,"25x23x15",IF(A1=0 ,0,"40x40x25")))
It is obvious that above construction will return syntax error.
Is it possible to make it work somehow?
Reply With Quote
  #2  
Old 01-06-2019, 01:26 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,819
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

To see how to do this and a wide range of other calculations in Word, check out my Microsoft Word Field Maths Tutorial, in the 'Sticky' thread at the top of this forum:
http://www.msofficeforums.com/word/3...-tutorial.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-06-2019, 01:42 PM
Charles Kenyon Charles Kenyon is online now Windows 10 Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 5,534
Charles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of light
Default

Bookmark that link!

Download and save the Field Maths tutorial. Extract it from the zip file and put it in your documents or in a location where you can find it again.

It is a valuable resource.

I try to review it on a regular basis because I forget this stuff.
Reply With Quote
  #4  
Old 01-06-2019, 01:46 PM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default

I checked this tutorial and hmm...there are some points I don't understand.
Tried "Testing and Returning Text Strings with Logical Functions in Bookmarks", but
seems bookmark can't contain numbers, so I it could not be referred to bookmarks in IF construction.
Tried the below:
IF A1="330 ml" "33x26x20" "" IF A1="250 ml" "25x22x15" "" IF A1=0,0,"39x26x25" \* MERGEFORMAT \* MERGEFORMAT \* MERGEFORMAT \* MERGEFORMAT
But field return only "ml".
Sample attached.
What I'm doing wrong?
Attached Files
File Type: docx sample.docx (11.8 KB, 2 views)
Reply With Quote
  #5  
Old 01-06-2019, 02:55 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,819
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Your field code constructions are nothing like what Word requires or what is described in the tutorial. You might start there. You certainly don't need any of those \* MERGEFORMAT switches.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 01-07-2019, 02:09 AM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default

But, this switches were inserted automatically.
Can you give me a tip what concrete section in the tutorial should I address?
I tried some constructions described in "returning text Strings", but still can't make it working...
Reply With Quote
  #7  
Old 01-07-2019, 03:18 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,819
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Word did not insert the \* MERGEFORMAT switches as a result of applying anything in the tutorial. What's the point of the SKIPIF field in cell B1?

Done properly, your IF field code would look like:
{IF{=A1}= 330 "33x26x20" {IF{=A1}= 250 "25x22x15" "39x26x25"}}
You could even use a field coded as:
{=A1-250 \# "'33x26x20'; '39x26x25';'25x22x15'"}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 01-07-2019, 04:16 AM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default

The second code worked fine. I simply opened formula dialogue:
Layout-->Formula, then pasted your code there and all worked.
I just can't figure out how to add checking:
if A1 is empty, then show nothing..


Also I tried The first one code, but I must be doing wrong again:
I go to Insert -> Click on Explore Quick Parts -> Field and choose IF field.
Then trying to paste your first code and nothing happens(.

I even trying to toggle field codes in B2 cell and edit directly, but still can't make them working.
Attached Files
File Type: docx sample.docx (12.0 KB, 0 views)
Reply With Quote
  #9  
Old 01-07-2019, 06:48 AM
Charles Kenyon Charles Kenyon is online now Windows 10 Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 5,534
Charles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of lightCharles Kenyon is a glorious beacon of light
Default

Quote:
Originally Posted by Strogg View Post
The second code worked fine. I simply opened formula dialogue:
Layout-->Formula, then pasted your code there and all worked.
I just can't figure out how to add checking:
if A1 is empty, then show nothing..


Also I tried The first one code, but I must be doing wrong again:
I go to Insert -> Click on Explore Quick Parts -> Field and choose IF field.
Then trying to paste your first code and nothing happens(.

I even trying to toggle field codes in B2 cell and edit directly, but still can't make them working.

To Insert an IF Field:
  1. Type "IF" without the quotation marks and select it.
  2. Press Ctrl+F9
  3. Insert your logical test, your truetext and falsetext


Any time you are having trouble with an IF field check:
  1. What is my logical test?
  2. What is my truetext?
  3. What is my (optional) falsetext?
  4. Note that if you nest fields, all braces must be inserted in pairs by Word, not typed.
Reply With Quote
  #10  
Old 01-07-2019, 09:55 AM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default

Quote:
Originally Posted by Charles Kenyon View Post
To Insert an IF Field:
  1. Type "IF" without the quotation marks and select it.
  2. Press Ctrl+F9
  3. Insert your logical test, your truetext and falsetext
Great! Thank you guys so much for your help and hints! Finally it works!
I even added check empty cell condition:
Code:
{IF{=A1}= 330 "33x26x20" {IF{=A1}= 250 "25x22x15"{IF{=A1}= 0 "" "39x26x25"}}}
(Sample attached).

But I assume that all of this will take too much time for everyday routine.
So I'd rather go using insert formula with Macropod's second code:

Code:
=A1-250 \# "'33x26x20'; '39x26x25';'25x22x15'"
Could the check of empty cell be added here as well?
Attached Files
File Type: docx sample.docx (12.2 KB, 0 views)
Reply With Quote
  #11  
Old 01-07-2019, 02:04 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,819
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Strogg View Post
The second code worked fine. I simply opened formula dialogue:
Layout-->Formula, then pasted your code there and all worked.
I just can't figure out how to add checking:
if A1 is empty, then show nothing..
For that you could use a formula coded as:
{IF{=A1}= 0 "" {=A1-250 \# "'33x26x20'; '39x26x25';'25x22x15'"}}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 01-07-2019, 03:13 PM
Strogg Strogg is offline Windows 7 32bit Office 2016
Novice
 
Join Date: Jan 2019
Posts: 6
Strogg is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
For that you could use a formula coded as:
{IF{=A1}= 0 "" {=A1-250 \# "'33x26x20'; '39x26x25';'25x22x15'"}}
I think I finally learned it. Thank you!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I make a slide in PPT return to start page automatically? sawe90 PowerPoint 1 09-15-2018 12:56 PM
make single letter top to bottom of page-Large famreeks Word 3 04-27-2017 05:40 PM
Substring to return numbers funkyfido Excel 4 07-22-2016 10:03 AM
How to make MATCH/INDEX formula return multiple successive responses. danigirl121 Excel 7 06-08-2016 01:57 PM
Words joined - how do i split? speaking Word 4 08-29-2011 12:24 PM


All times are GMT -7. The time now is 10:20 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft