Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-06-2019, 12:26 PM
Strogg Strogg is offline How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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 offline How make IF Formula return a numbers joined with a letter Windows 7 64bit How make IF Formula return a numbers joined with a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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:
https://www.msofficeforums.com/word/...-tutorial.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-06-2019, 01:42 PM
Charles Kenyon Charles Kenyon is online now How make IF Formula return a numbers joined with a letter Windows 10 How make IF Formula return a numbers joined with a letter Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
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 How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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, 8 views)
Reply With Quote
  #5  
Old 01-06-2019, 02:55 PM
macropod's Avatar
macropod macropod is offline How make IF Formula return a numbers joined with a letter Windows 7 64bit How make IF Formula return a numbers joined with a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 01-07-2019, 02:09 AM
Strogg Strogg is offline How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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 offline How make IF Formula return a numbers joined with a letter Windows 7 64bit How make IF Formula return a numbers joined with a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 01-07-2019, 04:16 AM
Strogg Strogg is offline How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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, 6 views)
Reply With Quote
  #9  
Old 01-07-2019, 06:48 AM
Charles Kenyon Charles Kenyon is online now How make IF Formula return a numbers joined with a letter Windows 10 How make IF Formula return a numbers joined with a letter Office 2016
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
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 How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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, 6 views)
Reply With Quote
  #11  
Old 01-07-2019, 02:04 PM
macropod's Avatar
macropod macropod is offline How make IF Formula return a numbers joined with a letter Windows 7 64bit How make IF Formula return a numbers joined with a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 01-07-2019, 03:13 PM
Strogg Strogg is offline How make IF Formula return a numbers joined with a letter Windows 7 32bit How make IF Formula return a numbers joined with a letter Office 2016
Novice
How make IF Formula return a numbers joined with a letter
 
Join Date: Jan 2019
Posts: 9
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
How make IF Formula return a numbers joined with a letter 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 make IF Formula return a numbers joined with a letter 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:40 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft