Microsoft Office Forums > Word How make IF Formula return a numbers joined with a letter
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-06-2019, 12:26 PM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9
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?
#2
01-06-2019, 01:26 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,480

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]
#3
01-06-2019, 01:42 PM
 Charles Kenyon Windows 10 Office 2016 Moderator Join Date: Mar 2012 Location: Sun Prairie, Wisconsin Posts: 5,863

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.
#4
01-06-2019, 01:46 PM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9

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
 sample.docx (11.8 KB, 2 views)
#5
01-06-2019, 02:55 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,480

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]
#6
01-07-2019, 02:09 AM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9

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...
#7
01-07-2019, 03:18 AM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,480

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]
#8
01-07-2019, 04:16 AM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9

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
 sample.docx (12.0 KB, 0 views)
#9
01-07-2019, 06:48 AM
 Charles Kenyon Windows 10 Office 2016 Moderator Join Date: Mar 2012 Location: Sun Prairie, Wisconsin Posts: 5,863

Quote:
 Originally Posted by Strogg 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

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.
#10
01-07-2019, 09:55 AM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9

Quote:
 Originally Posted by Charles Kenyon To Insert an IF Field: Type "IF" without the quotation marks and select it. Press Ctrl+F9 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
 sample.docx (12.2 KB, 0 views)
#11
01-07-2019, 02:04 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,480

Quote:
 Originally Posted by Strogg 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]
#12
01-07-2019, 03:13 PM
 Strogg Windows 7 32bit Office 2016 Novice Join Date: Jan 2019 Posts: 9

Quote:
 Originally Posted by macropod 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!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post sawe90 PowerPoint 1 09-15-2018 12:56 PM famreeks Word 3 04-27-2017 05:40 PM funkyfido Excel 4 07-22-2016 10:03 AM danigirl121 Excel 7 06-08-2016 01:57 PM speaking Word 4 08-29-2011 12:24 PM

All times are GMT -7. The time now is 05:07 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top